The Marksheet That Sorted Itself Wrong
A Class 10 teacher keeps a spreadsheet with 40 students. Columns are A Roll, B Name, C English, D Hindi, E Mathematics, F Science, G Social Science, H Total, I Percentage, J Result. The formulas are H2 =SUM(C2:G2), I2 =H2/5, and J2 =IF(H2>=150,"Pass","Fail"), filled down to row 41. Two students were absent in Mathematics and entered as AB; one new student was added by inserting a row at row 10, but H10, I10 and J10 remain blank. The teacher sorts A2:G41 by Name to prepare the award list, then finds that the totals no longer match the names. She also notices that an absent student's percentage is low. The school pass mark is 33% in each subject and 150 marks in total.
1. Explain why sorting produced mismatched totals and describe the correct sorting procedure.
Read the worked answer
The teacher selected only A2:G41. Columns H, I and J were outside the selected range, so the spreadsheet moved Rolls, Names and subject marks but left Total, Percentage and Result in their old rows. The totals now sit beside the wrong students.
Correct procedure:
- Undo the sort immediately if the original order is needed.
- Select the whole table, including headers, for example A1:J41, or use a named table/data range so all columns move together.
- Use Data → Sort, tick “My data has headers”, and sort by Name or by Roll as required.
- Before sorting, check that no rows are hidden or filtered out and that formulas use relative references within the same row.
- After sorting, verify a few known students against their marks and totals.
Sorting by Roll rather than Name is usually safer for a marksheet, because names can repeat or be spelled differently. Keep a copy of the unsorted sheet before any major change.
2. Rewrite the Total, Percentage and Result formulas so that absent students and the new row are handled correctly.
Read the worked answer
Use COUNT to detect missing subject marks and avoid dividing by 5 blindly.
- H2:
=SUM(C2:G2)sums the numeric marks and ignores the text AB. - I2:
=IF(COUNT(C2:G2)=5,H2/5,"Incomplete")calculates a percentage only when all five marks exist. - J2:
=IF(COUNT(C2:G2)<5,"Result pending",IF(AND(MIN(C2:G2)>=33,H2>=150),"Pass","Fail"))applies both the subject pass mark and the total pass mark.
After inserting row 10, copy H9:J9 into H10:J10, or use the fill handle from the row above, then confirm the references changed to row 10. Better still, convert the range to a table so new rows inherit formulas automatically. Finally, check the absence policy: if absent students are to be marked zero, enter 0 rather than AB, because that produces a different result from “Result pending”.
3. Why is typing totals manually risky, and what checks should the teacher make before printing the award list?
Read the worked answer
Manual totals break the link between marks and results: if a mark is corrected later, the typed total does not update, and a single keying error can change a grade without any warning. Formula totals recalculate automatically and can be audited.
Before printing, the teacher should:
- Recalculate and compare the sum of all subject totals with the sum of all marks.
- Check the COUNT of each row; any row with fewer than five numeric marks must be investigated.
- Verify the pass rule against the school's actual policy, including whether 33% is rounded and whether absentees are detained or given a re-test.
- Check the new row 10 and the last row 41, where fill operations most often fail.
- Sort by Roll, apply a filter for blanks, and inspect any student near the 150 or 33% boundary.
- Save a dated copy, print a draft, and have another teacher initial the draft before the final printout.