The Mark Sheet That Silently Dropped a Column
At a government-aided secondary school in Hooghly, the vocational IT teacher keeps one spreadsheet for the Class X ITeS batch of 42 students. The columns are A Roll, B Name, C Theory (out of 50), D Practical (out of 50), E Project (out of 20). Cell F2 held =C2+D2+E2, filled down to F43. Two weeks later she inserted a new column named Attendance between D and E, entered marks out of 10, and expected the Total column to become Theory + Practical + Attendance + Project. She then noticed that the Total column still adds only three mark columns and quietly leaves out Attendance; row 27 shows #VALUE! because 'AB' was typed in the Project cell; and a name-based lookup for 'S. Mondal' returned the wrong student's project mark because two students share that name. She now wants a clean printed mark sheet sorted by total, plus a word-processed notice for guardians.
1. Explain why the totals broke after the column was inserted, and give the exact formula you would now place in the Total column. How would you make it survive a future column insertion and an 'AB' entry?
Read the worked answer
Why it broke: the Total was built from individual references, =C2+D2+E2. When a column is inserted inside or to the right of a referenced range, the software shifts those references. The old Project column moved from E to F, so the formula became =C2+D2+F2. It still adds three cells, so it looks correct, but Attendance is omitted - a silent error, worse than a visible one. The #VALUE! appears because direct arithmetic meets text ('AB').
Fix: in the new Total column use =SUM(C2:F2) and fill down.
SUMignores text, so 'AB' no longer breaks the row.- Because the range covers all mark columns, inserting a new column inside C:F extends the range automatically.
Guard rail: add a check column =IF(COUNT(C2:F2)<4,"Review","OK") so missing marks are flagged rather than hidden. Then sort by Roll (or by Total on a copy), freeze the header row, and print - a printed sheet must be traceable, not just arithmetically right.
2. Why did the name-based lookup return the wrong student, and what would you use instead?
Read the worked answer
Names are not keys. Two students share 'S. Mondal', so an exact-match lookup on Name returns the first occurrence only - the spreadsheet is behaving correctly; the design is wrong. This is a data-modelling mistake, not a formula mistake.
Use Roll number as the unique key. Keep the lookup table with Roll in the leftmost column: =VLOOKUP($A2,$A$2:$F$43,6,FALSE). A more insertion-proof option is =INDEX($F$2:$F$43,MATCH($A2,$A$2:$A$43,0)), because it does not depend on counting columns.
Verify the key before trusting it: COUNTIF($A$2:$A$43,A2) must return 1 for every row; anything above 1 means duplicate keys. Also check for trailing spaces, which break exact matching. If the notice must show a name, build a helper column 'Roll – Name' for display only.
3. The teacher wants a printed mark sheet sorted by total and a guardian notice. Which work should be done in the spreadsheet and which in the word processor, and why?
Read the worked answer
Split by purpose. Marks, calculations and sorting belong in the spreadsheet; the notice belongs in the word processor. Sorting in the word processor would not carry the linked data reliably and would break the merge order.
Sequence:
- Clean the sheet - unique Roll, numeric marks, 'AB' handled, check column clean.
- Save an untouched master file, then make a working copy.
- Sort the copy by Total (descending) for the printed sheet; sort back by Roll for the notice, or keep two copies.
- In the word processor, create the notice with merge fields for Roll, Name and Total, connect it to the sheet, and preview at least three records - including the duplicate-name pair - before printing.
Export the merged batch to PDF first and check alignment and pagination. The word processor gives paragraphs and formatting; the spreadsheet remains the single source of truth.