The Grade Sheet Where Averages Look Wrong
A teacher builds a class results sheet. Column A is student name, columns B–F are five test scores, column G is the average. For one student the five scores are 80, 90, blank, 70, 100. The teacher types =AVERAGE(B2:F2) in G2 and gets a result higher than they expect - they expected the blank to count as zero and drag the average down. A colleague says 'just use SUM/5 instead'. There are 30 students, and several have one or two blanks because they were absent.
1. Explain what AVERAGE does with the blank and whether SUM/5 would be more correct here.
Read the worked answer
AVERAGE ignores blank cells (and text), so it divides by the count of numeric entries, not by 5. For 80,90,blank,70,100 the average is (80+90+70+100)/4 = 85, not 340/5 = 68. Whether that is 'correct' depends on intent: if a blank means absent and should not penalise, AVERAGE is right; if a blank means scored zero, then SUM/5 is right. The judgement call is to decide what a blank represents before choosing the formula, and to make that explicit. Using SUM/5 blindly would treat every absence as a zero and unfairly lower grades.
2. Recommend a defensible approach for the whole sheet and how to make blanks visible.
Read the worked answer
Decide and document the policy. If absences should not count, keep =AVERAGE(B2:F2) but make blanks explicit by entering a marker (e.g. 'ABS') so they are not silently ignored - though note text is also ignored by AVERAGE, so better to use =AVERAGEIF or a COUNT check. A robust formula: =IF(COUNT(B2:F2)=0,"",SUM(B2:F2)/COUNT(B2:F2)) to avoid divide-by-zero and to average only entered scores. To surface missing data, apply conditional formatting highlighting blanks in B2:F2 so the teacher sees which students are affected before trusting the averages.