Established onExcel 365, Windows: cosmetic adjustment on the last operationExcel: 15 significant digitsGoogle Sheets, web: comparable, adjustment less documented
Spreadsheets store numbers in the same binary floating-point format as almost everything else, and that format cannot represent most decimal fractions exactly. One tenth is not expressible as a finite sum of halves, quarters and eighths, so what is stored is the nearest value the format can hold.
What that looks like in practice
The difference is around one part in ten thousand million million, which is invisible in any single cell and is not invisible after enough arithmetic.
Both major products hide much of it. Excel applies a cosmetic adjustment on the final operation of a calculation, so a comparison of nought point one plus nought point two against nought point three returns true, while writing the same comparison as a subtraction and testing against zero does not. The adjustment is documented by the vendor and is the reason this behaves inconsistently rather than wrongly.
The separate fifteen-digit limit
A different constraint with a similar appearance. Excel keeps fifteen significant decimal digits and zeroes everything after them, so a sixteen-digit identifier entered as a number loses its last digit permanently on entry.
This affects account numbers, card numbers, long reference codes and identifiers of every kind, and it is not reversible: the digits are not stored anywhere. The remedy is to make the column text before the data arrives, which the entry on leading zeros in the import part covers.
Where it costs money
Three places. A comparison of two computed amounts for equality, which fails when the amounts differ in the sixteenth digit. A running total across many rows, where the small differences accumulate in one direction. And a rounded display, where the cell shows a tidy figure and the underlying value is not tidy, so a total of displayed values differs from a displayed total.
That last one produces the classic reconciliation of a penny that nobody can find, and it is not a mistake by anybody. It is the difference between rounding at the end and rounding at each step.
The other arithmetic that surprises people
Dates are numbers. A date is stored as a count of days from a fixed origin, which is why subtracting one date from another gives a number and why formatting a number as a date shows something plausible.
Both major products contain a deliberate error in that count: they treat 1900 as a leap year, which it was not, in order to remain compatible with a spreadsheet from the early nineteen-eighties that had the bug. Dates before the first of March 1900 are therefore off by one, and this is documented by the vendor as intentional.
Google Sheets inherits the same behaviour for compatibility. It is a small thing that almost never matters and it is worth knowing because it is the clearest example in the subject of a defect preserved on purpose for forty years, which the fourth part returns to when it discusses why files outlive their assumptions.
What to do about it
Round deliberately and say where. If amounts are currency, apply rounding to two decimal places at the point where a value becomes an amount somebody will be paid, and not at every intermediate step, and record which cells do it.
Never compare computed values for equality. Compare the absolute difference against a small tolerance instead. This is standard practice in every other numerical field and is almost never done in spreadsheets.
Do not rely on the display. Showing two decimal places changes what is drawn and not what is stored. Where the displayed figures must add up, the values must be rounded, not formatted.
For identifiers, use text. Anything that is not arithmetic should not be a number, including postcodes, account numbers and product codes, however numeric they look.
The setting that is not the answer
Excel offers an option to use the displayed precision as the stored precision for the whole workbook. It removes the reconciliation problem and permanently discards precision from every value in the file, including intermediate results that needed it, and it cannot be undone. It is occasionally the right choice and is usually reached for by somebody who has not read what it does.
What we cannot verify
The storage format, the fifteen-digit limit and the cosmetic adjustment are all documented by the vendor and demonstrable in any copy of the software. Google Sheets behaves comparably and its documentation is less specific about the adjustment, so the exact conditions under which it applies there were established by testing rather than from a published statement.
The practical rule, stated once
Currency is not a floating-point quantity that happens to have two decimal places. Treat it as a whole number of minor units wherever the arithmetic is more than a single multiplication, and convert for display only.
In short
- Most decimal fractions have no exact binary representation, so what is stored is the nearest value.
- Excel adjusts the final operation cosmetically, which makes the behaviour look inconsistent.
- A separate limit keeps fifteen significant digits and zeroes the rest on entry.
- Equality comparisons, running totals and rounded displays are the three places it costs money.
- Round where a value becomes an amount, compare against a tolerance, and use text for identifiers.
- The precision-as-displayed setting discards precision from the whole file and cannot be undone.