Spreadsheets

Spreadsheets calculate using formulas that reference cells, e.g. =A1+B1, or functions like =SUM(A1:A10) to add a range of cells, and =AVERAGE(A1:A10) to find a mean. You need to be able to read a formula and work out what value it will produce.

When a formula is copied to other cells, a relative reference (e.g. A1) automatically adjusts to match its new position, but an absolute reference (e.g. $A$1, using dollar signs) stays fixed on the same cell. Forgetting to fix a reference to, say, a tax rate cell means it ‘shifts’ incorrectly when the formula is copied down.

Typical mistakes include: copying a formula without adjusting/fixing references correctly, including or excluding the wrong range of cells in a SUM (e.g. missing the last row), circular references, and formatting errors that hide the true value being used (e.g. a cell displaying a rounded value but calculating with the unrounded one).

Before trusting a spreadsheet's output, check: does the total look plausible? Do a few cells manually to verify the formula logic. Check that ranges in SUM/AVERAGE functions include all the intended rows/columns and nothing extra. A single small error copied across a whole sheet can badly distort every result.

(a) Because D2 is a relative reference, when the formula =B2*D2 is copied down to C3, it will automatically change to =B3*D3 — but D3 is empty (the rate was only entered once, in D2), so the calculation will use 0 instead of 5%, giving £0 for every row except the first.

(b) The formula should use an absolute reference for the rate cell: =B2*$D$2, so that when copied down, it always refers back to D2, keeping the commission rate fixed at 5% for every row.

1. A relative reference automatically changes/adjusts to match its new position when a formula is copied; an absolute reference (marked with $ signs, e.g. $A$1) stays fixed on the same cell no matter where the formula is copied.
2. The last value (in A6) will be excluded from the sum, so the total will be understated/incorrect (missing one value).
3. =AVERAGE(B2:B20).
4. The cell is likely displaying a rounded value (£4.50 to 2 decimal places) for readability, but the actual stored value used in calculations is the unrounded figure (£4.4978) — formatting only changes how a number looks, not the value stored/used.
5. Because it's a relative reference, it will shift to a different (likely empty or incorrect) cell in each row as it's copied down, so only the first row will correctly use the VAT rate — every other row will produce a wrong (probably zero or error) result.