The Shared Budget Workbook That Doubles Its Totals
A treasurer maintains a monthly budget workbook. Column A holds 40 category names. Column B holds the amount budgeted for each category. In B41 they type =SUM(B2:B40) and the total comes out exactly double what they expect. They check the individual cells in B2:B40 and every amount is correct and appears once. They notice that row 1 contains the title 'Monthly Budget', and that the values in B2:B40 are all typed with a leading apostrophe, e.g. '450. The treasurer insists the SUM formula must be wrong because 'it can't add a title'.
1. Diagnose the doubled total and state what the SUM formula is actually adding.
Read the worked answer
The apostrophe forces each amount to be stored as text, not a number. SUM ignores text, so it is not adding B2:B40 at all - the doubled figure is coming from somewhere else in the range or from the formula's own structure being misread. The key judgement: SUM silently skips text, so a total that is too high cannot be caused by text values being included; text values would make it too low (treated as zero). The real cause must be found by inspecting the formula and the range, not by assuming the apostrophes inflate the sum. The correct first action is to remove the apostrophes (or use Data > Text to Columns / VALUE) and re-check, because until the cells are numeric, any SUM result is untrustworthy.
2. The treasurer wants a reliable total. Describe how to convert the text amounts to real numbers and verify the result.
Read the worked answer
Select B2:B40 and use Data > Text to Columns > Finish (or Find & Replace the apostrophe with nothing) to coerce the text to numbers. Then verify: the values should right-align and a status-bar selection of B2:B40 should show a Sum matching B41. As a cross-check, add a helper =COUNT(B2:B40) - it should return 39 if all 39 amounts are now numeric. Only when COUNT confirms numeric values should the SUM total be trusted. This avoids 'fixing' the formula when the data type is the true fault.