This is not wrong; it computes the average correctly. B54＝[B52*B53] means “the number of students in that class × the average score of that class” ＝ the total score of that class. Copying it to C54 to G54 and then using H54＝[sum(B54 to G54)] gives the total score of all students, and H53＝[H54/H52] gives total score ÷ total number of students, which yields the correct overall average.
×Incorrectイ
This is not wrong; it computes the average correctly. sum(B2 to G36) is the total score of all students, adding up the scores of all six classes, and H52 is the total number of students according to description (2). Therefore [sum(B2 to G36)/H52] is exactly total score ÷ total number of students, that is, the overall average as defined.
×Incorrectウ
This is not wrong; it computes the average correctly. average(B2 to G36) is the average of the values in the rectangular range B2 to G36, and the average function of spreadsheet software does not count blank cells (rows with no student) in the number of items. Therefore the numerator is the total score of all students and the denominator is the number of cells that actually contain scores ＝ the total number of students, giving the same value as イ.
○エ
正解Choice エ, the formula [average(B53 to G53)], simply averages again the six class averages of classes P through U — a so-called “average of averages”. The average score of all students should be obtained as total score of all students ÷ total number of students, but a simple average treats every class with the same weight (1/6) regardless of how many students it has. The fact that description (1) deliberately enters the number of students of each class into B52 to G52 also shows that the classes do not necessarily have the same number of students. For example, if a class of 40 students averages 60 points and a class of 10 students averages 80 points, the correct overall average is (40×60＋10×80)÷50＝64 points, whereas the average of averages is (60＋80)÷2＝70 points — the two do not match. This formula happens to match only when every class has the same number of students, so the one that cannot correctly compute the average score is エ.
ここがポイント
An overall average is obtained as the total of everything ÷ the count of everything. An “average of averages”, obtained by simply averaging the group averages again, does not match the overall average unless the groups have the same size (weight).
The average function of spreadsheet software does not count blank cells in the number of items, so even a rectangular range such as average(B2 to G36) is divided by the actual number of data items. Keep in mind that it is equivalent to the form total ÷ count.