The Marksheet That Broke Before the Result Meeting
St Anne's Senior Secondary School runs four sections of Class XII. The examiner builds one marksheet: column C is theory out of 80, D is practical out of 20, E is total =C2+D2, F is percentage =E2/100, and row 43 holds =AVERAGE(E2:E41). Two students were absent for the theory paper and were typed as AB. One practical mark was entered as 105 instead of 15. On printing, the total and percentage columns show #VALUE! for the two absentees, the class average in E43 also shows #VALUE!, and the student who actually scored 12 in theory now stands first in the rank list. The examiner has 40 minutes before the result meeting, must not lose the original entries, has to explain the sheet to the principal, and the file is shared with two teachers who may type in it. The principal wants absentees shown as 'Absent', not zero. The sheet has no data validation and no protected cells.
1. Explain why one text entry in the theory column breaks E43 and the rank order, then give corrected formulas for total, percentage, class average and rank that keep absentees visible without corrupting totals.
Read the worked answer
Why it breaks
C5+D5where C5 contains AB returns#VALUE!. An error is a value, not text, so it travels into F5.AVERAGEignores text but does not ignore errors. One#VALUE!inside E2:E41 makes E43 return#VALUE!. This distinction is what most candidates miss.- The rank changed because 105 is a perfectly valid number. No error appears, so the wrong practical quietly scores highest. Error traps catch wrong types, never wrong magnitudes.
Corrected formulas (row 2, copied down)
- E2:
=IF(OR(C2="AB",D2="AB"),"AB",C2+D2) - F2:
=IF(E2="AB","AB",E2/100) - E43:
=AVERAGEIF(E2:E41,"<>AB")- the text returned by IF is excluded and no error remains. - Rank:
=IF(E2="AB","-",RANK.EQ(E2,$E$2:$E$41))so absentees are not ranked.
This preserves the original entries, shows absentees as text, and stops two absent rows from destroying the class average. Check the percentage denominator: 100 is the maximum total here, so it is correct, but if the maximum were 105 it should sit in an absolute cell such as $H$1.
2. The principal says absentees should be counted as zero so the class average is 'honest'. Argue the point and state exactly what the sheet should display.
Read the worked answer
Treating absent as zero is a policy choice, not a correction. With two absentees out of 40, averaging with zeros answers 'marks per enrolled student'; averaging only present students answers 'how the candidates who sat performed'. Both are honest if labelled; neither is honest if hidden inside an error.
The sheet should therefore show two clearly captioned columns: Average (present) using AVERAGEIF(...,"<>AB"), and Average (all enrolled, absent = 0) using a formula that substitutes 0. The principal states which one is read at the meeting, and the choice is written in a note cell so the next examiner repeats it.
For the individual absentee, print 'Absent' in total and percentage and leave rank blank. Printing 0 would be read as a mark of zero and could be carried wrongly into migration or scholarship paperwork. Consistency across sections matters more than which convention is chosen, so record the rule in the file header rather than deciding it again next term.
3. Recommend two controls to stop the 105-type error recurring, and explain why each catches a different failure.
Read the worked answer
Control 1 - Data validation. Select D2:D41, allow whole numbers between 0 and 20, add an input message stating 'practical out of 20' and a stop-style error alert. A typed 105 is then refused at entry. This catches out-of-range values only.
Control 2 - A control total reconciled against the source. The practical award list has a known total; put =SUM(D2:D41) in a cell and compare it with that total before printing. This catches a 15 typed as 51 or a 15 typed as 51 in the right range - a transposition that validation happily accepts.
They are complementary because validation checks each value against a rule, while the control total checks the whole column against an independent figure; neither alone is sufficient. Add conditional formatting to flag any D value above 20 for rows already filled, and protect the sheet with entry cells unlocked and formula cells locked so the two teachers can type marks without overwriting formulas. Keep the original workbook unchanged, work on a copy, and save a dated version before the meeting.