The Marksheet That Would Not Update
A COPA Semester II batch of 24 trainees is completing a spreadsheet exercise in the theory room before moving to the lab. The marksheet holds five subject marks in columns B to F, a total in column G, a percentage in column H and a grade in column I. The maximum-marks figure is typed once into cell B28 (value 500), and the grade table sits in J2:K6. Two problems surface when the instructor projects the submitted files. First, a trainee changes B28 from 500 to 600 to simulate a revised scheme, and every percentage in column H stays exactly as before. Second, in 17 of the 24 files every grade cell shows #N/A even though the marks are clearly present; the instructor sees that cell I25 contains a VLOOKUP whose table range is J25:K29 rather than J2:K6. The theory period has 40 minutes left and the practical assessment is tomorrow morning.
1. Diagnose the two symptoms and give the corrected formulas for H2 and I2, explaining why the originals failed.
Read the worked answer
Symptom 1 - percentages do not update. The trainees typed the constant 500 into the formula (something like =G2/500*100). B28 is therefore decorative; changing it changes nothing. The fix is a reference, and specifically an absolute one so that copying down does not drift: =G2/$B$28*100.
Symptom 2 - #N/A in every grade cell. The lookup table range was entered as J2:K6 (relative). Copied down, both the row and column anchors shift, so the row-25 copy looks at J25:K29, which is empty, and an approximate-match VLOOKUP on an empty range returns #N/A. The fix is =VLOOKUP(H2,$J$2:$K$6,2,TRUE).
Second-order check the instructor must not skip: with the TRUE (approximate) argument, the first column of the table must be sorted ascending. If the grade thresholds in J are stored descending (90, 75, 60, 40, 0), the corrected formula will still return wrong grades rather than #N/A, which is a harder error to spot. Either re-sort the table ascending or convert it to exact-match banding.
2. Plan the 40-minute theory period so that trainees can transfer the idea rather than memorise two formulas. Include the check you would use to prove transfer.
Read the worked answer
0–5 min - make the failure visible. Change B28 live on the projector and let the batch watch nothing happen. Ask for a show of hands on why. Do not answer yet.
5–15 min - one idea, two jobs. Demonstrate on a two-cell sheet: $ locks a position, and copying is what exposes it. Show the same formula copied across and down with and without anchors, side by side.
15–25 min - repair own files. Each trainee fixes H2 and I2 in their own sheet, then deliberately copies to row 25 and to column J and predicts the result before pressing Enter. Prediction before action is the point.
25–35 min - contrast the two uses. Absolute for a single fixed parameter (the maximum in B28); absolute for a whole lookup block ($J$2:$K$6). Ask what would happen if only J were locked (J$2:K$6) - it still breaks when copied across.
35–40 min - transfer check (on paper, new data). Give a fresh mini-sheet with a tax rate in D1 and a slab table in F2:G6 and ask trainees to write the two formulas and to state what would break if the formula were dragged to column E. Marking this, not the repaired file, tells you who has the concept.
3. A trainee objects that 'the software should be smart enough to know what I meant'. How do you answer that in one minute without dismissing the point?
Read the worked answer
Accept the premise partly, then convert it into a design idea. Spreadsheets are deliberately literal because the same copy operation must serve both 'move with me' and 'stay fixed' - a total row that shifts and a tax rate that must not shift appear on the same sheet. If the software guessed, it would sometimes guess wrong silently, and silent wrong answers in a marksheet are worse than visible ones. The $ is the trainee telling the software which of the two is meant; it is a control, not a chore. Close by noting that modern tools do offer structured references and named ranges (for example naming B28 as MaxMarks and writing =G2/MaxMarks*100), which is the same idea expressed more readably - a good extension task for the stronger trainees.