Problem I. 350. (May 2014)
I. 350. We would like to store the data and process the results of a football championship with 12 football teams by using a spreadsheet application. You can use 3 sheets, Csapatok (teams), Körmérkőzés (tournament) and Eredmények (results). Auxiliary computations can be performed below or to the right of the data and results of the present task (by leaving one blank row or column). Macros or your own programs should not be used.
1. You should write some team names into cells A1:A12 of the sheet Csapatok (one can decide what teams to invite to the championship).
2. You should create 2 tables on the sheet Körmérkőzés according to the example. The left table should contain the number of scored goals and received goals during each match, whereas the right table should contain the same information in a form ``scored \(\displaystyle \text{goals} : \text{received}\) goals''. You should fill the left table with random integers between 0 and 8 as in the example. A \(\displaystyle -1\) value should be present in the table where no match can take place. Similarly, you should put \(\displaystyle -1\) values in the appropriate cells in the right table. By using conditional formatting, both the text and background colors of a cell containing \(\displaystyle -1\) should be set to grey.
3. You should give the team names in the range A2:A13 of the sheet Eredmény. Next to it, in column B you should compute the scored goals, in column C the received ones, and in column D the goal difference.
4. Columns E, F and G should contain for each team the number of won, lost or tied matches.
5. Column H should contain the total number of points for each team. Won, tied and lost matches are worth 2, 1 and 0 points, respectively.
6. Column I should contain the team ranking based on the number of team points. If the team points are equal, the ranking is based on a better goal difference.
7. The header of the sheet Eredmény in cells B1:I1 should be set according to the previous tasks.
You can get the maximal number of points for this task only if you use copyable formulae to describe regions containing the same type of data. You should use the same simple border type as in the example in your solutions to the above tasks or when separating your auxiliary computations. The spreadsheet i350 (.xls, .xlsx, ...) containing the solution should be submitted.
(10 pont)
Deadline expired on June 10, 2014.
Sorry, the solution is available only in Hungarian. Google translation
Mintamegoldásként Németh Balázs jászberényi, 8. osztályos (i350nb.xlsx) és Fényes Balázs 11. évfolyamos budapesti tanuló megoldását (i350fb.xls) adjuk közre.
Statistics:
12 students sent a solution. 10 points: Fényes Balázs, Kovács 246 Benedek, Kovács Balázs Marcell, Mócsy Miklós, Németh 123 Balázs, Radnai Bálint. 9 points: Gercsó Márk. 8 points: 3 students. 5 points: 1 student. 3 points: 1 student.
Problems in Information Technology of KöMaL, May 2014