Why Every Student Shows Pass: Debugging a Marks Workbook
A +2 Vocational Computer Technique section has 30 students. The class teacher builds a workbook with columns A roll number, B name, C theory marks out of 70, D practical marks out of 30, E total, F percentage and G result. In E2 she types =C2+D2 and copies it to E31. In F2 she types =E2/100 and formats the column as percentage. The pass mark 33 is typed in H1, and G2 contains =IF(F2>=H1,"Pass","Fail"), copied down to G31. Student 12 was absent for theory and the clerk entered AB in C12. The teacher also wants a class average in E32 using =AVERAGE(E2:E31). During verification she finds that every student is marked Pass, E12 shows #VALUE!, and E32 also shows #VALUE!. She blames Excel and wants to retype all 30 rows by hand. The student assistant is asked to explain what the formulas are actually doing, fix them without retyping data, and advise how absentees should be handled so the average is meaningful.
1. Explain why every student is shown Pass and why E12 and E32 show #VALUE!. Give corrected formulas for E2, F2, G2 and E32 that can be copied down without retyping data.
Read the worked answer
H1 is a relative reference. When G2 is copied to G3, the reference shifts to H2, then H3, and so on. Those cells are empty, and Excel treats an empty cell as 0 in a comparison, so F2>=0 is always true and every row returns Pass. The fix is to anchor the pass mark: =IF(F2>=$H$1,"Pass","Fail").
E12 uses =C12+D12. The + operator cannot add text, so AB causes #VALUE!. E32 then averages a range containing that error, so the error propagates. SUM ignores text, so =SUM(C2:D2) in E2 avoids the error. A more informative version is =IF(COUNT(C2:D2)=2,SUM(C2:D2),"ABSENT"), which makes the absence visible instead of hiding it.
Corrected set: E2 =IF(COUNT(C2:D2)=2,SUM(C2:D2),"ABSENT"); F2 =IFERROR(E2/100,""); G2 =IF(F2="","Absent",IF(F2>=$H$1,"Pass","Fail")); E32 =AVERAGE(E2:E31). AVERAGE ignores text such as ABSENT or an empty string, so the average then covers only students with numeric totals. The rows do not need retyping; only the formulas need replacing and filling down.
2. The teacher wants the average of students who actually appeared, and a separate count of absentees. Give formulas and explain why a simple AVERAGE over a column containing zeros for absentees is misleading.
Read the worked answer
Count of absentees: =COUNTIF(C2:C31,"AB"). Average of those who appeared: =AVERAGEIF(C2:C31,"<>AB",E2:E31). If absentees are stored as blanks or as the text ABSENT in column E, =AVERAGE(E2:E31) also works because AVERAGE ignores text and blanks.
The problem with entering 0 for an absentee is that zero is a real number and is included in the denominator. With 28 present students scoring an average of 60 and two absentees entered as 0, the reported average falls to 56, which misrepresents the class. It also makes the result ambiguous: a genuine zero mark and an absence look identical.
Judgement: decide the policy first. If the absentee should be counted as failed in the result column, show Fail, but keep the total blank or ABSENT so that the class average is computed on candidates who appeared. State the rule on the mark sheet, for example 'average of candidates who appeared', so the number is not challenged later.
3. A student's percentage displays as 33% but column G says Fail. Is Excel wrong? Recommend a rule the teacher can defend.
Read the worked answer
Excel is not wrong; the display is. If the total is 32.5, F contains 0.325. Formatting to zero decimal places shows 33%, but the stored value used by IF remains 0.325. The comparison 0.325>=0.33 is false, so the result is Fail. Formatting changes appearance, not value.
The teacher must choose whether the pass mark applies to exact marks or to rounded marks. If rounding is the policy, compare the rounded value: =IF(ROUND(F2*100,0)>=33,"Pass","Fail"). Equivalently, compare total marks directly: =IF(E2>=33,"Pass","Fail"), which avoids a percentage step entirely. If exact marks are the policy, keep the current comparison but display one or two decimals so the screen agrees with the decision.
Judgement: the dangerous option is to round only for display and compare the unrounded value, because the mark sheet then appears to contradict itself. Pick one rule, write it in the workbook header, and apply it uniformly to all 30 rows.