An invoice with ten lines, each with VAT calculated and shown to two decimal places. You add up the displayed values by hand and get 1,234.56; the sheet shows 1,234.57. Neither of you is wrong: the sheet adds the real values, with all their decimals, while you added the ones rounded on screen. It is one of the most common problems in sheets that handle money, and it is solved by understanding the difference between showing a rounded number and actually rounding it.
Number format does not change the value
When you set a cell to two decimal places, the program shows the rounded value but keeps and uses the full value in calculations. If a cell contains 10.456 and you display it as 10.46, the formula =A1*100 returns 1045.6, not 1046.
This behaviour is correct and in most calculations it is what you want: rounding at every step would introduce cumulative errors. It becomes a problem when intermediate values have to match real amounts, such as invoice lines, instalments in a plan or shares in an allocation.
The ROUND function and its variants
To actually change the value you use a function:
| Function | Behaviour | Example with 2.345 |
|---|---|---|
ROUND(x, 2) |
to the nearest, 5 rounds up | 2.35 |
ROUNDUP(x, 2) |
always away from zero | 2.35 |
ROUNDDOWN(x, 2) |
always towards zero | 2.34 |
MROUND(x, 0.05) |
to the nearest multiple | 2.35 |
TRUNC(x, 2) |
drops the extra decimals | 2.34 |
A few useful details:
- the second argument can be negative:
=ROUND(1234, -2)returns 1200, handy for estimates and quotes; - MROUND is for "nearest 5 cents" prices or times rounded to the quarter hour:
=MROUND(A2, 15/1440)rounds a time to the nearest 15 minutes; - INT and TRUNC match for positive numbers but not for negatives:
INT(-2.5)returns -3,TRUNC(-2.5)returns -2.
Where to round
The practical question is not "whether" to round but "at which point in the calculation". Back to the invoice:
- Round each line and then add: the total matches the sum of the printed values, but may differ by a few cents from a calculation on the total.
- Add the full values and round only the total: the total is more precise, but does not match the sum of the displayed lines.
Neither is wrong in absolute terms; it depends on the rule the document has to follow. For invoices, the tax calculation rule should be checked with whoever keeps the accounts; the sheet must reproduce that rule explicitly, with ROUND in the right place.
Allocations that do not add up
Splitting 100 euros into three equal shares produces 33.33 + 33.33 + 33.33 = 99.99. Rounding each share does not help: one cent is always missing. The standard solution is to calculate every share except the last and give the last one the difference:
Shares 1 and 2: =ROUND(Total/3, 2)
Share 3: =Total-SUM(previous shares)
That way the sum of the shares always equals the total. The same pattern applies to repayment schedules: the last instalment absorbs the rounding differences.
The "precision as displayed" option
Excel offers a setting, among the advanced options, called "Set precision as displayed". When switched on, the program permanently replaces values with those shown by the format. It looks convenient but it is risky: it acts on the whole workbook, changes the data irreversibly and makes the file's behaviour depend on cell formatting. It is better to use ROUND where needed, explicitly and visibly.
Floating point
There is a second, subtler reason why numbers do not add up. Spreadsheets store numbers in binary floating-point format, with about 15 significant digits of precision. Many decimals that are exact to us, such as 0.1, have no finite representation in binary, just as 1/3 has none in decimal.
The result is that some calculations produce tiny differences. For example =0.1+0.2-0.3 may return a minuscule non-zero value instead of 0, visible only when you increase the decimals a lot. The program hides many of these cases, but not all.
The practical consequences:
- a comparison such as
=A2=B2can return FALSE for two values that look identical; - conditional formatting set to "equal to zero" may not trigger;
- an exact lookup on calculated values may find no match.
The solution is to round before comparing: =ROUND(A2, 2)=ROUND(B2, 2), or to check that the difference is below a threshold: =ABS(A2-B2)<0.005.
For the same reason, long numeric codes such as card numbers or some tracking codes, which exceed 15 digits, should always be stored as text: otherwise the final digits are replaced with zeros.
In my work, in any sheet that produces amounts to be carried elsewhere, I add a final check comparing the total with the sum of the rounded parts: if the difference is not zero, a cell turns red.
In short
- Number format shows a rounded value but calculations use the full one.
- ROUND and its variants really change the value; choose carefully where to use them.
- In allocations give the last share the difference, so the total always adds up.
- Avoid the "precision as displayed" option: it is irreversible.
- Because of floating point, round before comparing and store codes over 15 digits as text.
