Spreadsheet Errors in a School Cooperative
A school cooperative in Sabha keeps its notebook sales in a spreadsheet. Columns are A date, B item, C quantity, D unit price, E total. Row 1 has headers; data run from row 2 to row 19. The formula in E2 is =C2*D2 and is copied down. Several E cells show #VALUE!. Inspection shows C7 contains '5 pcs', C12 contains '3 packs', and C15 is blank. Row 14 is hidden; it records a returned order of 20 notebooks, and the coordinator hid it 'so it would not spoil the total'. Cell E20 contains =SUM(E2:E19). The coordinator reports total sales of 1,840 dinars from the visible rows. A volunteer suggests fixing the #VALUE! errors by deleting the affected rows, and another suggests typing the correct totals manually in column E for those rows.
1. Diagnose the #VALUE! errors and compare the two suggested fixes.
Read the worked answer
#VALUE! appears because E7 and E12 multiply a number by text ('5 pcs' and '3 packs'); a blank quantity in C15 multiplies as zero and would not normally produce #VALUE!. Deleting the affected rows would remove real sales records and break the row references used by the total, so it destroys data. Typing totals manually would remove the formula from those rows, so later price or quantity changes would not update them, and there is no audit trail. The better fix is at the source: enter 5 and 3 as numbers in C7 and C12, keep C15 blank or enter 0, and apply data validation to column C so that only whole numbers are accepted. A formula such as =IF(ISNUMBER(C2),C2*D2,0) can prevent an error display, but it should be used with a visible warning, not as a substitute for cleaning the data.
2. Explain why the reported total of 1,840 dinars may be wrong even after the #VALUE! errors are fixed, and recommend a formula or procedure.
Read the worked answer
The hidden row 14 is still inside the range E2:E19, and =SUM(E2:E19) includes hidden rows. Hiding a row does not remove its value. If row 14 records a return entered as a positive quantity, the SUM adds 20 notebooks to sales and overstates the total; if it is entered as a negative quantity, the SUM is arithmetically correct but the coordinator's visible-row total of 1,840 may exclude it. The business rule must be decided first: returns should reduce sales, so row 14 should be visible and entered as a negative quantity, or kept separate in a returns column. If the intention is genuinely to sum visible rows only, =SUBTOTAL(109,E2:E19) ignores hidden rows, but that would also hide the return from the total. The correct procedure is to reconcile E20 with the visible rows, make the return explicit, and then use SUM.
3. The coordinator wants to raise all prices by 10% next term. Compare editing column D directly with adding a new price column.
Read the worked answer
Editing column D directly would overwrite the historical prices, so past totals could no longer be checked and the cooperative would lose its audit trail. Adding a new column, for example F for 'new price', with =D2*1.1 copied down preserves the original data. The total column can then be changed deliberately to use the new price for future rows, or a separate price table can be used with an absolute reference. The new column should be formatted as currency to two decimal places, and the change should be dated in a note or header. The judgement is that a spreadsheet used for records should separate raw data from calculations; overwriting source data is fast but destroys the evidence needed to verify the 1,840-dinar figure and any later correction.