Assessment sheet where SUM hides the absent subject
Ganesh Secondary School, Biratnagar, keeps Class 10 internal assessment marks in a spreadsheet. Row 1 holds headings: A1 Roll, B1 Name, C1 Nepali, D1 English, E1 Mathematics, F1 Science, G1 Total, H1 Percentage, I1 Result. Data run from row 2 to row 43 (42 students) and every subject is marked out of 100.
The class teacher typed =SUM(C2:F2) in G2, =G2/400*100 in H2 and =IF(H2>=40,"Pass","Fail") in I2, then filled all three down. Three students were absent in one subject and the letter A was typed in those cells. Their totals still look plausible, but the printed record for Sabina (Nepali 85, English 78, Mathematics A, Science 90) says Pass. The Mathematics teacher objects: the school rule is that a student must score at least 35 in every subject. Separately, the examination committee has lowered the pass mark to 35 for this year only, and the class teacher refuses to edit 42 formulas by hand.
1. Explain exactly what the spreadsheet did with Sabina's 'A', why the printed result is misleading, and give a better way to record an absent subject.
Read the worked answer
SUM applied to a range quietly ignores text - it does not raise an error, which is why nobody noticed. So G2 = 85 + 78 + 90 = 253, H2 = 253/400*100 = 63.25, and I2 prints Pass because 63.25 ≥ 40.
The record is misleading in both directions at once. The 253 pretends to be a total out of 400 when only three papers exist, yet averaged over the papers Sabina actually sat, the same marks give 84.3. A reader of the sheet can take her as a weak candidate or a strong one, and the absent subject has vanished from view entirely.
Better options:
- Enter 0 and note the absence in a comment cell - honest total, but the zero still drags the percentage down.
- Leave the cell empty and let a formula count the papers written:
=IF(COUNT(C2:F2)<4,"Incomplete",SUM(C2:F2)/400*100). This is the best of the three, because the sheet then says Incomplete rather than awarding 63.25% to a candidate who never sat Mathematics.
2. Write the formula for I2 that enforces the rule 'at least 35 in every subject'. Then explain why simply adding an AND of the four subjects still lets Sabina through.
Read the worked answer
=IF(COUNT(C2:F2)<4,"Incomplete",IF(AND(C2>=35,D2>=35,E2>=35,F2>=35),"Pass","Fail")), filled down to I43.
The original formula only checks the overall percentage, so three strong subjects pull the average above 40 and a failed subject disappears into the total.
The trap is the second part of the question. Writing =IF(AND(C2>=35,D2>=35,E2>=35,F2>=35),"Pass","Fail") alone does not fix Sabina: in spreadsheet comparisons, any text is treated as greater than any number, so "A">=35 evaluates TRUE and the AND still returns Pass. The COUNT guard must come first, or each test must be wrapped in ISNUMBER. This is exactly the kind of check a data-entry sheet needs - the rule is 'all four subjects present and all four at least 35'.
3. The pass mark is now 35. Show how to change it once for all 42 rows, with the exact formula, and explain why the dollar signs are needed.
Read the worked answer
Type 35 into a spare cell, say K1, and write:
=IF(COUNT(C2:F2)<4,"Incomplete",IF(AND(C2>=$K$1,D2>=$K$1,E2>=$K$1,F2>=$K$1),"Pass","Fail"))
Then fill down to row 43. Next year the committee edits K1 only.
Why the dollars matter: C2 is a relative reference - when filled down it becomes C3, C4 and so on, which is exactly what is wanted because each row holds a different student. Without dollars, K1 would also shift to K2, K3, K4, and rows below K1 are empty; an empty cell compares as 0, so every student would be compared against 0 and would pass. $K$1 locks both the column and the row, so every copied formula still points at the one pass-mark cell. The general rule: relative when the reference should move with the row, absolute when it must stay fixed.