The Exam Results Sheet That Will Not Add Up
A junior clerk at a government junior college maintains an Excel workbook, sheet InterResults, for 42 Intermediate Commerce students in rows 3 to 44. Column A holds roll number, B name, C English, D Second Language, E Commerce and F Economics, each out of 100; G Total, H Percentage, I Result and J Grade. The pass rule is 35 marks or more in every subject. The clerk built column G by typing the total for rows 3 to 25 and typed the rest as formulas, and filled column I with the formula =IF(G3>=140,"Pass","Fail") copied from row 3 to row 33, typing the last eleven rows by hand. Two marks were entered as text with a leading apostrophe, E18 showing '78 and F31 showing '62, and nobody noticed because the totals for those students had been typed rather than calculated. The scale in M2:N7 lists grade cut-offs from 0 to 90 in ascending order. The clerk ran =COUNTIF(I3:I44,"Fail") and got 6, but the class teacher insists 8 students failed. Results are to be announced in two days and the principal wants a printed sheet.
1. Explain why COUNTIF returns 6 when the true number of failures is 8, and identify the second, silent error in the sheet that could already have produced wrong results.
Read the worked answer
Cause 1 - the pass rule itself is wrong. =IF(G3>=140,"Pass","Fail") tests the total, but the rule is 35 or more in every subject. A student who scores 30 in English and 90, 88 and 85 elsewhere reaches 293 out of 400 and is labelled Pass, even though a subject-wise minimum has been failed. Two such students push the true failure count to 8. COUNTIF is reporting the sheet correctly; the sheet is reporting the students incorrectly. This is the more serious error because it silently marks failing students as passed and would be hard to defend in an appeal.
Cause 2 - text stored as numbers. The apostrophe forces Excel to store '78 and '62 as text. A typed total hides the consequence, but any SUM or COUNTIF touching the raw marks will ignore them. If a formula total is later applied, those two students' totals will drop by 78 and 62 respectively, and a total-based Pass/Fail check may classify them wrongly in the other direction. Worse, COUNTIF with a numeric criterion such as "<35" treats text as neither less than 35 nor greater than it, so text marks are simply invisible.
How to confirm: select C3:F44 and look at the status bar count of numeric cells - it should be 168 (42 × 4); anything less means text. Also use =COUNT(H3:H44) and compare with 42, and filter column I to see exactly which 8 rows the teacher is counting and compare with the 6 in the sheet.
2. Write the formulas for Total, Percentage, Result and Grade, and explain why the lookup needs an absolute range and a TRUE argument.
Read the worked answer
Assuming the marks sit in C3:F3 and the scale in M2:N7:
- G3: =SUM(C3:F3)
- H3: =ROUND(G3/4,2) - percentage out of 400. Format as a number, not as a percentage, since the value is already out of 100.
- I3: =IF(MIN(C3:F3)>=35,"Pass","Fail") - MIN enforces the subject-wise minimum. This is the correct rule; a total test is not.
- J3: =VLOOKUP(H3,$M$2:$N$7,2,TRUE) - with IFERROR wrapped round it if the sheet may contain blanks or text: =IFERROR(VLOOKUP(H3,$M$2:$N$7,2,TRUE),"Check")
Why the absolute range: when J3 is copied down to J44, a relative reference M2:N7 would slide to M3:N8, M4:N9 and so on, so students further down the sheet would be graded against the wrong cut-offs - usually with no error message. The dollar signs lock the table.
Why TRUE: the fourth argument TRUE tells Excel to find the largest value in the first column that is less than or equal to the lookup value. A student at 78 has no exact row in the table; TRUE finds 75 and returns grade A. FALSE would demand an exact match and return #N/A for almost every student. The price of TRUE is that the first column must be sorted in ascending order - 0, 35, 50, 60, 75, 90. If the table is unsorted, VLOOKUP returns a plausible but wrong grade with no warning, which is exactly the kind of error a printed results sheet cannot afford.
3. The clerk proposes to retype the two text marks and correct fifteen totals by hand tonight because the deadline is in two days. Advise on the safer approach and on what should be done before printing.
Read the worked answer
Reject the hand-fix as the primary method, not because it is slow but because it leaves no trace and cannot be checked. Fifteen typed corrections on a sheet that has already proved unreliable is a recipe for a wrong printed result, and errors on published marks are expensive to reverse. The two text marks must still be converted, but that is a repair step, not the repair strategy.
Safer sequence, achievable in one evening:
- Save a copy of the original file untouched as an audit copy; work on a duplicate.
- Re-enter the two text marks as numbers (or use a helper column with =VALUE(E18) and paste values back). Then verify that the numeric count in C3:F44 is exactly 168.
- Do not type totals. Enter the formulas in row 3 and fill down the whole range G3:J44 in one action, so every row is treated identically and no row is left as a hand-typed number.
- Add a control row: count of Pass plus count of Fail must equal 42; count of grades must equal 42; and a flag column =IF(G3<>SUM(C3:F3),"Check","") will catch any surviving typed total.
- Add Data Validation (whole number between 0 and 100) on C3:F44 so future marks cannot enter as text.
- Compare the new failure list of 8 against the teacher's list before printing, and record the correction in a short note kept with the file.
The judgement behind all this is that formula-driven uniformity plus a reconciliation check is auditable, while speed of typing is not. If time is very short, fixing the pass rule is the non-negotiable item - the wrong classifications are already in the sheet and will be published.