The Marks Register That Silently Went Wrong
Neha maintains the marks register for a coaching class of 48 students in a spreadsheet. Row 1 has headers: A Roll No, B Name, C Physics, D Chemistry, E Mathematics, F Total, G Percentage, H Result, I Grade. In row 2 she types =C2+D2+E2, =F2/300*100, =IF(G2>=40,"Pass","Fail") and a nested IF for grades A to D, then fills down. Halfway through the year the class adds Biology, so she inserts a new column before F. Later she admits five new students in rows 50 to 54 and notices that the pass-count cell =COUNTIF(H2:H49,"Pass") still shows the old figure and the grade pie chart has not changed. A second sheet fetches names with row-aligned references such as =Marks!B2. After Neha sorts the main sheet by Total, descending, several names on that second sheet no longer match their roll numbers. The register looks correct on screen, which is why nobody has noticed the Biology marks.
1. Explain precisely what inserting the new column before F did to each formula, why =SUM(C2:E2) would not have rescued the Total, and how the sheet should be rebuilt.
Read the worked answer
What moved. The insert pushes Total to G, Percentage to H, Result to I and Grade to J, and puts Biology in F. Excel rewrites references automatically - that is the trap.
=C2+D2+E2still adds exactly C, D and E. It does not error; it silently returns a total that ignores Biology. A visible error would have been safer.- The percentage formula lands in H2 and becomes
=G2/300*100. The numerator is now the (already wrong) total and the denominator is still 300 when the maximum is 400. - The Result and Grade formulas move to I and J and now test the wrong percentage cell unless Excel updated them to H - verify, because filled-down formulas with mixed relative references can drift.
Why SUM would not have saved it. A range like SUM(C2:E2) grows only when the inserted column falls strictly inside the range, for example between C and D. Inserting at F is exactly on the boundary, so the range stays C2:E2 and Biology is still excluded. Silent wrongness again.
Rebuild.
- Set Total to
=SUM(C2:G2)(columns C through G inclusive of Biology). - Set Percentage to
=Total/400*100, and put the denominator 400 in its own labelled cell referenced with an absolute address so next year only one cell changes. - Convert the data range to a Table (Ctrl+T) and use structured references, which expand automatically when rows or subject columns are added.
- Spot-check two students by hand and compare with a calculator before releasing marks.
2. Diagnose why the pass count and the pie chart ignored the five new students, and explain the trade-offs of the three ways to fix it.
Read the worked answer
Cause. Both objects hold a fixed range captured when they were created: COUNTIF(H2:H49,"Pass") and a chart series pointing at H2:H49. New rows 50–54 are outside that range, so they are invisible to the formula and to the chart. Nothing warns the user, because a range that stops early is syntactically valid.
Three fixes, with trade-offs.
| Fix | How | Trade-off |
|---|---|---|
| Extend the range | Change to H2:H54 (or H2:H200 as headroom) | Quick, but must be redone every admission; headroom rows can distort averages if formulas count blanks |
| Whole-column reference | =COUNTIF(H:H,"Pass") |
Never breaks on new rows; careless use on text columns can match unintended cells and it is slower on very large files |
| Excel Table (Ctrl+T) | Use structured reference such as COUNTIF(Table1[Result],"Pass"); charts bound to the Table auto-extend |
Best long-term, but the Table must be the single source and nothing may be typed below it |
Judgement: for a register that grows every term, the Table is the right answer; extending ranges is acceptable only as an emergency fix. Also confirm the chart's source range, not just the formula - extending COUNTIF does not extend the chart. Finally, check the criteria: COUNTIF is case-insensitive and matches whole cell text, so a trailing space in a Result cell ("Pass ") is counted as a non-match and quietly reduces the count.
3. The second sheet now shows mismatched names after sorting. Explain why this happened and how the lookup should be designed so that sorting, filtering and new rows cannot break it.
Read the worked answer
Why sorting broke it. The second sheet fetched names with row-aligned references like =Marks!B2, which encode the assumption 'roll number 1 is always on row 2'. Sorting by Total, descending, rearranges the physical rows, so row 2 now holds whichever student has the highest total. Every row-aligned reference now points at a different student. Sorting does not corrupt data; it exposes a formula that depended on position rather than on identity.
The correct design - join on a key, not on a row number.
- Keep Roll No unique on the main sheet; if it is not unique, no lookup can be trusted.
- On the second sheet write the key (Roll No) in a cell, say A2, and look the name up:
=VLOOKUP($A2, Marks!$A$2:$B$200, 2, FALSE)- the$locks the table array so copying down cannot drift, and FALSE forces an exact match. Approximate match (TRUE) on an unsorted or re-sorted key column is a classic source of wrong names.- Or
=INDEX(Marks!$B$2:$B$200, MATCH($A2, Marks!$A$2:$A$200, 0)), which survives column insertions on the main sheet because it does not count columns.
- Wrap with
IFERROR(...,"Not found")so a deleted or mistyped roll number shows a flag instead of a plausible wrong name.
Judgement: false names are worse than blank cells, so an explicit 'Not found' is a feature. Sort and filter the main sheet freely afterwards - a key-based lookup is indifferent to row order.