A Khasra Spreadsheet That Prints the Wrong Total - and a File With No Safe Copy
A records clerk maintains a spreadsheet of 1,200 khasra entries for a village. Column A is khasra number, B owner, C area in kanal, D cultivator, E remarks. The total of column C is typed in row 1205 as =SUM(C2:C1204). Later she pastes three late entries at rows 1206 to 1208, below the total row, and then sorts the block A2:E1205 by owner name. The printed copy she hands to the Tehsildar still shows the old total, and a line reading 'total' now appears in the middle of the list. The file exists only on her desktop; she emails it from her personal account to an outside consultant and protects it with the password 'tehsil123', which is also her email password.
1. Why did the printed total not change after the three late entries were added? Give two structural fixes.
Read the worked answer
The formula is =SUM(C2:C1204). When data is appended at rows 1206 to 1208 - below the formula's last row - those cells lie outside the range, so the sum never sees them. The error is one of layout, not of arithmetic: the total sits inside the data area, which means any later addition lands on the wrong side of it.
Two fixes, either of which ends the problem permanently:
- Convert the range to an Excel Table (select A1:E1204, Insert > Table). A structured reference such as =SUM(Table1[Area]) expands automatically when rows are added above or below.
- Move the total out of the data block - place it on a summary sheet, or a fixed row well below with a wide range such as =SUM(C2:C5000), so new rows fall inside the range.
Whichever is used, reconcile with a count: COUNTA of column A should equal the number of entries the Jamabandi actually contains.
2. Why did the 'total' line jump into the middle of the list after sorting, and what is the record-keeping risk?
Read the worked answer
Sorting A2:E1205 treated row 1205 as just another data row. Excel sorted it by the owner-name column along with everything else, so the total row was carried to wherever its blank or text value placed it. The printed output therefore shows a total in the middle of the list and leaves the bottom of the list untotalled.
Risk: anyone reading the printout may take the mid-list figure to be a subtotal, or may add the visible closing figure believing it covers all 1,203 rows. A land record whose stated total is wrong by an unknown amount is worse than one with no total, because it looks authoritative. Remedy: keep totals outside the sorted block, and before any release check that the total equals the sum of the visible column and that the row count matches.
3. Rank the three most urgent changes to how this file is handled, with the reason for each.
Read the worked answer
- Get a second copy off the desktop, today. A single copy on one machine is lost to a disk failure, theft or a stray overwrite; there is no recovery route. Save the master to the office server or approved storage with dated versions, and keep a weekly offline copy.
- Change the password and stop reusing it. 'tehsil123' protects both the file and the email account, so one compromise exposes both, and the pattern is guessable. Use a distinct passphrase for the file, a different one for email, and send the file in a password-protected archive with the password conveyed by another channel - never in the same email.
- Use the official email account and log the share. Personal email for official records breaks the office's record of who received what; send from the departmental account, note the date, recipient and purpose, and obtain an acknowledgement.
Only after these should the cosmetic spreadsheet fixes be addressed.