Problem I. 171. (November 2007)
I. 171. You should evaluate a school survey using a spreadsheet application. The data is found in the worksheet ``Skills''. The first rows of the sheet (with the exception of the first cells) contain a short (one-word) description of the skills surveyed. For example, cell B1 contains ``Speech'', C1 ``Writing'', D1 ``Counting'' and so on. Seven skills have been surveyed. Students in this test attended the same grade, and were from classes ``A'' to ``F''. There are at most 300 students. Beginning with the second row of the same worksheet, there are the class letters of each student and his or her results; so column A contains one of the following: Class A, Class B, ..., Class F, then the consecutive columns contain the results for each skill as an integer between 1 and 100, or 0 (or empty cell) if the student was not present when that skill was tested.
The worksheet ``Evaluation'' contains information on how to evaluate each skill and sort students into five groups. Suppose, for example, that ``Counting'' is divided into the following categories: Group I contains students with 1---24 points, Group II from 25 to 45 points, Group III from 46 to 58, Group IV from 59 to 80, while Group V from 81 to 100. Then, beginning with its first column, one of the first 7 rows of the sheet contain the values ``Counting'', ``24'', ``45'', ``58'', ``80'' and ``100''. Thus, the first column is the name of the skill, then the six other columns contain the largest possible values of each of the 5 groups. However, the order of the skills on worksheets ``Skill'' and ``Evaluation'' may be different.
You should make a third worksheet ``Results'' so that one can easily read off answers to the following questions:
the number of students that have been surveyed, from each class, or according to different skills, or altogether
how many students are there in the five groups of each skill, from each class or altogether
what will be the order of the classes if sorted according to each skill, or all skills together (``Class X'' precedes ``Class Y'', if Class X has more students in the group of the highest value (``Group V''), or, if this number of students is the same, then in the group just below it (``Group IV'') and so on).
You should submit the spreadsheet (i171.xls, i171.ods, ...) and a short documentation (i171.txt, i171.pdf, ...) containing a description of your solution, further, the name and version number of the spreadsheet application.
Deadline expired on December 17, 2007.
Sorry, the solution is available only in Hungarian. Google translation
A feladatra egy teljes és hibátlan megoldás született: Véges Márton, budapesti versenyző munkája (i171.xls) .
7 students sent a solution. 10 points: Véges Márton. 7 points: 1 student. 6 points: 1 student. 5 points: 3 students. 2 points: 1 student.