Sooner or later, every spreadsheet meets the missing penny. The invoice says £847.29; your sheet says £847.30. Finance wants to know why. Nobody’s stolen anything — but something is wrong, and it’s worth understanding properly, because the fix is a rule you’ll apply for the rest of your career.
Displayed rounding: the usual suspect
Most missing pennies are the costume problem. Three lines each hold £10.005; the two-decimal format shows £10.01 three times, which eyeballs to £30.03 — but the values sum to £30.015, displaying as £30.02. Every visible number is “right”; the total disagrees with the arithmetic your reader does in their head.
The rule that ends it: when a number becomes real — invoiced, paid, reported — round the value, not the costume.
=ROUND(B2 * 1.2, 2)
ROUND(x, 2) makes the stored value exactly two decimals. Now
the column genuinely contains what it appears to contain, and
sums match sight. The VAT line on an invoice, the per-payslip tax,
the interest each month in
the amortisation table
— each gets rounded at the moment it becomes a real amount of
money, and totals reconcile to the penny forever after.
The counter-rule matters equally: don’t round intermediate maths. Round the monthly interest before charging it, yes — that’s a real amount. But round a growth rate mid-model and you’ve injected error that compounds through every later row — the pension curve drifts for no visible reason. Real amounts: round. Working figures: leave at full precision.
The family, and who they’re for
ROUND rounds halves away from zero (10.005 → 10.01). Its
siblings each have one honest job:
| Function | Job | Typical customer |
|---|---|---|
ROUNDUP(x, 2) |
always up | charges — nobody bills £0.014 as £0.01 |
ROUNDDOWN(x, 0) |
always down | “complete units only” — full boxes, whole years of service |
MROUND(x, 0.05) |
nearest multiple | cash rounding, 5-minute timesheets |
CEILING / FLOOR |
multiples, directional | price points (£x.99), pack sizes |
INT(x) |
drop the decimals | ages, whole days from dates |
The choice among them is a policy, not a maths question — up,
down or nearest is somebody’s rule about fairness. Which is why
rounding policy belongs visible in the sheet (a comment, or
a named cell
like round_to), not buried as a magic number in a formula.
And the deeper oddity: 0.1 + 0.2
Occasionally you’ll test =0.1+0.2=0.3 and get FALSE, and it’s
worth knowing why once: computers store numbers in binary, and
0.1 in binary is a repeating fraction — like ⅓ in decimal. Excel
holds it to about 15 significant digits, so tiny residues like
0.30000000000000004 exist under the costume. This is every
computer, not an Excel bug — and it’s the other reason
long chains of unrounded arithmetic drift by a penny.
Practical consequences, both cheap: never compare calculated
decimals with = in logic
— compare ROUND(a,2)=ROUND(b,2), stating your tolerance; and
treat any reconciliation that’s out by less than a penny as
arithmetic residue to be rounded away, while anything out by
exactly a tidy amount (1p per row, £0.05 per invoice) is a
policy mismatch — your rounding rule differs from their
rounding rule, and now you know where to look.
The missing penny was never missing. It was sitting in the third decimal place, waiting for someone who knew the difference between what a cell shows and what it holds — which, as of this lesson, is you.