The Kirana Shop Register That Will Not Add Up
A kirana shop owner in Nagpur keeps his monthly sales register in a spreadsheet. Columns are A Date, B Item, C Qty, D Rate, E Amount, and E2 contains =C2*D2, filled down to E200. Some Rate cells were typed with stray characters, such as 45/- and ₹38, and those rows now show #VALUE!. The owner added 40 new items last week by typing in rows 201 to 240, but the grand total in E150, which uses =SUM(E2:E200), has not changed and the spreadsheet now warns about a circular reference. There is also a discount cell, H1, containing 12%, and F2 contains =E2*H1, filled down to F200; every row below F2 shows a discount of zero. The owner wants the sheet fixed before he shows it to his lender.
1. Explain why the `#VALUE!` errors appear and describe how to repair those Rate cells without retyping the whole column.
Read the worked answer
#VALUE! appears because the multiplication =C2*D2 is being given text, not a number. When a cell begins with a currency symbol or an apostrophe, or ends with /-, the spreadsheet stores it as text. The Qty cell is numeric, so the formula is asked to multiply a number by text, which is undefined - hence the error.
The repair is to convert those cells back to numbers, not to retype the column:
- Select the Rate column and use Find & Replace to delete the stray characters (
₹,/-), working on the column only so the rest of the sheet is untouched. - If a cell still behaves as text, use Data > Text to Columns on that column and finish with the General format, or enter
=VALUE(D2)in a helper column, then copy and Paste Special > Values back over D. - Apply the Currency or Number format afterwards, so the ₹ sign is a format rather than part of the data.
After the conversion, check one known row by hand - for example, 10 kg at ₹45 must give 450 - and confirm that the #VALUE! count drops to zero before touching the totals.
2. Why is the grand total ignoring the 40 new rows, and why does a circular reference warning appear? Propose a layout that prevents both problems.
Read the worked answer
The total formula reads =SUM(E2:E200). Rows 201 to 240 lie outside that range, so the 40 new sales are simply not included - the total is arithmetically correct for the range it covers, and wrong for the register. This is a range that was fixed when the sheet was small and never revisited.
The circular reference has a separate cause: the total sits in E150, which is inside E2:E200. The formula asks the sum of a range that contains the sum, so the cell depends on itself and cannot resolve.
A layout that prevents both: keep transaction data in a contiguous block (say rows 2 to 5000), and place the grand total below the data, outside the range - for example in E5002, with =SUM(E2:E5000). Better still, select the block and press Ctrl+T to make it an Excel Table; totals then use structured references such as =SUM(Sales[Amount]) and expand automatically when new rows are typed. Never place a total inside the range it is summing, and always add new records inside the defined block rather than past its last row.
3. Every discount below F2 shows zero. Diagnose the cause and state the corrected formula, explaining the difference between the two reference types.
Read the worked answer
F2 contains =E2*H1. In this form, H1 is a relative reference: when the formula is copied to F3, the reference shifts to H2; in F4 it becomes H3, and so on. Cells H2, H3 and below are empty, and an empty cell is treated as zero in multiplication - so every copied row computes E×0 = 0. Only F2, which still points at H1, gives the right answer. That is why the first row looks correct and the rest do not.
The corrected formula is =E2*$H$1, filled down. The dollar signs make it an absolute reference: the column and the row are locked, so every copied row still reads the discount from H1.
The distinction in practice: use a relative reference (E2) for data that should move with each row, and an absolute reference ($H$1) for a single input cell such as a discount rate, tax rate or exchange rate. A useful checksum before showing the lender: F2 should equal 12% of E2, and the sum of column F should equal 12% of the sum of column E.