The Gradebook That Breaks When a Student Is Absent
At a matriculation school in Madurai, a teacher maintains the Term-1 mark register in a spreadsheet. Row 1 holds headers: A Roll No, B Name, C Tamil, D English, E Maths, F Total, G Percentage, H Grade. Row 2 is the maximum mark row: C2, D2 and E2 each contain 50, and B2 contains the label Max. Data for 60 students occupies rows 4 to 63. In D9 the teacher typed AB for a student who was absent, and F9 now shows #VALUE!. F9 contains =C9+D9+E9, while F10 contains =SUM(C10:E10) and works. The teacher also wants the percentage to divide by the maximum total, but B1 holds the school name. Grades are A+ for 90% and above, A for 80–89%, B for 70–79%, and U below 70. The teacher needs a reliable class average that ignores absent students.
1. Explain why F9 shows #VALUE! while F10 works, then give corrected formulas for F9 and G9 that treat absent as absent rather than zero. The maximum total is in C2:E2.
Read the worked answer
F9 is =C9+D9+E9. The + operator requires numbers, so the text 'AB' in D9 makes the whole expression #VALUE!. F10 uses SUM, and SUM ignores text in a referenced range, which is why it still totals. Rewrite F9 as =IF(COUNT(C9:E9)=3,SUM(C9:E9),"AB") or =IF(ISNUMBER(D9),SUM(C9:E9),"AB"); the COUNT version also catches multiple missing marks. G9 should divide by the maximum total, not by B1, which holds the school name: =IF(ISNUMBER(F9),F9/($C$2+$D$2+$E$2),"AB"). The $ signs lock the maximum row when the formula is copied down. If preferred, put =SUM(C2:E2) in a dedicated cell and refer to it as $B$3. The key reasoning is that absent must propagate as a label, not be coerced to 0; otherwise the percentage and grade are wrong.
2. Write a formula in H9 for the grade bands A+ ≥90%, A 80–89%, B 70–79%, U below 70, and explain why condition order matters.
Read the worked answer
=IF(G9="AB","AB",IF(G9>=0.9,"A+",IF(G9>=0.8,"A",IF(G9>=0.7,"B","U")))). Nested IF evaluates from the outside in and stops at the first TRUE. Testing G9>=0.9 first is safe; testing G9>=0.7 first would send a 95% student to B, because 95% also satisfies >=70%. The absent label must be handled before numeric comparisons, or text may be compared with numbers and return a wrong band. An alternative is a lookup table with lower bounds 0, 0.7, 0.8, 0.9 in ascending order and =VLOOKUP(G9,$J$2:$K$5,2,TRUE); approximate-match VLOOKUP requires ascending order and would still need an IF for 'AB'. The nested IF is easier to audit for four bands.
3. The teacher wants the class average percentage that ignores absent students, plus a count of absent entries. Give formulas and explain what happens if the total column contains text.
Read the worked answer
If the corrected F column contains the label 'AB' for absent students, count them with =COUNTIF(F4:F63,"AB"). For the average of numeric percentages, use =AVERAGEIF(G4:G63,">=0"). AVERAGE itself ignores text in a range, but it does not ignore error values; if F still contains #VALUE!, that error propagates into any formula that references it, so F must be corrected first. To average totals and then divide by the maximum, use =SUMIF(F4:F63,">=0")/COUNTIF(F4:F63,">=0")/($C$2+$D$2+$E$2). The reasoning is that 'ignore absent' means exclude the student from both numerator and denominator. Treating absent as 0 would lower the class average and is statistically wrong.