The 4.2-Mark Gap in the Consolidated Marks File
You maintain the consolidated marks file for 120 students across three sections. Each section sheet has columns A to H, with the total in column H. The section B mean on your consolidated sheet reads 58.4, while the section teacher's own summary says 62.6. You find three things: (i) the consolidated formula is =AVERAGE(B!H2:H41) although section B has 42 students; (ii) fourteen totals in section B were pasted as text (left-aligned, no error shown); (iii) a colleague emailed 'final_marks_final_v2.xlsx' and also saved over the shared folder copy, so two files now exist with different timestamps but no version history. Results must be uploaded to the portal tomorrow at 10 a.m., and the portal locks after submission.
1. Diagnose why the two means differ. Give the order in which you would check, and explain why stopping at the first explanation you find is unsafe.
Read the worked answer
Order of checks.
- Row range. Section B has 42 students; H2:H41 covers 40 rows, so two students are excluded entirely. Identify them and note their totals.
- Text entries. AVERAGE silently ignores totals stored as text, so the divisor shrinks. Fourteen cells is a large fraction of 42 and will move the mean substantially. Convert with Text to Columns or VALUE(), then confirm with SUM/COUNT and COUNTA against the row count.
- Provenance. Establish which of the two files produced 62.6; the section teacher may be quoting an earlier draft that included marks still missing from your copy.
Why not stop at the first explanation. The row-range error and the text-entry error push the mean in different directions depending on whether the omitted students scored high or low. Finding one cause that happens to point the right way does not prove you have found all of them, and the two effects can partly cancel.
Method. Rebuild the section from row-level data: 42 rows, all totals numeric, recompute the mean, and reconcile to 62.6 before accepting any figure. If you cannot reconcile at row level, you do not have a mean; you have an output.
2. Design the submission workflow for tomorrow morning, including which file you send and what safeguards you would put in place so this cannot recur next term.
Read the worked answer
Before 10 a.m. tomorrow.
- Rebuild a single master from confirmed row-level data (42 rows per section, 120 total), with formulas written over the true last row.
- Add a checksum block per section: COUNT, COUNTA, SUM, AVERAGE, MAX. Every section teacher must confirm their checksum by email before you upload.
- Choose which file to send by reconciliation, not by filename or timestamp: the file whose checksums match the teachers' confirmations is the file that goes up. Discard the other, but keep it read-only.
- Save a read-only PDF of exactly what was uploaded, because the portal locks and you will need evidence of what was submitted.
Safeguards for next term.
- One master template with the row count fixed at entry time, and =AVERAGE over the full intended range.
- Data validation on marks: whole numbers 0 to 100 only, with a rejection message, plus conditional formatting that flags any text in a total column.
- One named editor per section; naming convention YYYY-MM-DD_section_marks_vN; shared folder with version history instead of USB or email attachments.
- Never submit a figure you cannot reproduce from row-level data in under ten minutes.