Using spreadsheets to calculate average scores in nonstandard situations

Igor Fulman
Department of Mathematics and Statistics
ASU

Abstract: I will consider the following situations.

(1) A student didn't turn in one or more homework assignments, and the roster shows blanks in corresponding places. How to calculate the average?
The function AVERAGE wouldn't work properly: it would find the average of existing grades only, ignoring the blanks.

(2) I promise to students that I would drop one lowest grade, and find the average of the rest. How to calculate the avegare now? This is not
very difficult by itself (use the function MIN), but it becomes difficult when combined with (1). Now, the function MIN would give
the lowest of existing grades, rather than the lowest of all grades which should be 0.

(3) Same as (2) but now I promise to drop two lowest grades. Now it becomes even more complicated.

(4) A student turned in one or more assignments that you can't grade either because you never received them (but you trust the student that he
actually turned them in), or because your grader was too late with grades. (This happens too!) In this case I promise students that I would ignore
the missing assignments. For example, if the grades were 10, 8, 9, 7, 8, [missing], 9, [missing], I would ignore the two missing assignments
and calculate the average through the rest. If I also drop 2 lowest grades, I would drop 8 and 7 and calculate the average through the
rest: (10 + 9 + 8 + 9)/4 = 9. How would you configure Excel to do this automatically? How about combining this feature with (1) and (2) or (3)?

I have solved all these problems for myself, by using very long and complicated formulas. For example, the formula solving the situations
(1), (3), and (4) simultaneously, looks like this:

=IF(COUNTBLANK(C1:C12)=0;SUM(C1:C12)-MIN(C1:C12)-SMALL(C1:C12;2);
IF(COUNTBLANK(C1:C12)=1;SUM(C1:C12)-MIN(C1:C12);SUM(C1:C12)))/
(10+COUNT(C1:C12)-COUNTA(C1:C12))

I have been using such formulas for several semesters now.