Everyday Accuracy Fixes
Why your total is off by a cent
Three numbers in a column, each one showing 10.00. The total underneath says 30.01. You check the numbers, they are fine. You check the formula, it is a plain SUM. Nothing is broken.
Excel is doing exactly what you asked. The problem is that you asked for something slightly different from what you meant.
Excel shows you one number and stores another
When you format a cell to two decimal places, you have changed the display and nothing else. The number underneath keeps every digit it had. SUM adds the stored numbers, not the ones on your screen.
So a column holding 10.004 three times looks like this:
| Stored value | Shown at 2dp |
|---|---|
| 10.004 | 10.00 |
| 10.004 | 10.00 |
| 10.004 | 10.00 |
| 30.012 | 30.01 |
Your eye adds the right hand column and gets 30.00. Excel adds the left hand column and shows you 30.01. Both are correct. That is the whole mystery.
Nobody notices this on three rows. On a reconciliation with four hundred rows it turns into a variance you spend an afternoon hunting.
The fix is to round the number, not the display
If you want what you see to be what gets added, change the value itself:
That returns 10.00 as a real number, not as a costume. Sum a column of those and the total agrees with the column.
The second argument is the part nobody explains
Everyone learns ROUND(number, 2) and stops there. The second argument is a position, and it can go either side of the decimal point:
| Formula | Result | What it did |
|---|---|---|
| =ROUND(1234.567, 2) | 1234.57 | Nearest hundredth |
| =ROUND(1234.567, 1) | 1234.6 | Nearest tenth |
| =ROUND(1234.567, 0) | 1235 | Nearest whole number |
| =ROUND(1234.567, -2) | 1200 | Nearest hundred |
| =ROUND(1234.567, -3) | 1000 | Nearest thousand |
Negative digits are the one to remember. Rounding a schedule to the nearest thousand for a board pack is =ROUND(A2, -3), not a format change and not a division.
The rest of the family
| Function | What it does | Use it when |
|---|---|---|
| ROUND | Nearest, halves go away from zero | The default. Most of the time this is the one. |
| ROUNDUP | Always away from zero | You must never understate a figure. |
| ROUNDDOWN | Always towards zero | You must never overstate a figure. |
| MROUND | Nearest multiple you name | Pricing to the nearest 0.05, hours to the nearest 0.25. |
| CEILING.MATH | Up to a multiple | Packaging, batch sizes, anything you cannot buy a fraction of. |
| FLOOR.MATH | Down to a multiple | Discount bands, tiered thresholds. |
| INT | Down to the next whole number | Whole units, positive numbers. |
| TRUNC | Chops the decimals off | You want the digits gone, not rounded. |
INT and TRUNC look identical until a negative number turns up. INT(-2.5) gives -3, because it goes down. TRUNC(-2.5) gives -2, because it just deletes the decimals. If your data has credits and refunds in it, that difference is a real one.
Round once, and decide where
The cent usually appears because two different rules got mixed in one workbook. There are only two sensible options and you have to pick:
- Round each line, then add. This is what invoices, tax and anything that has to tie to a printed document need. The line items are the truth.
- Add first, round the total once. This is what analysis and forecasting want, because every intermediate rounding throws away a little accuracy and the errors compound.
Both are defensible. Doing one in column D and the other in column H is how you end up explaining a variance to someone who does not care why.
There is a setting under File, Options, Advanced called Set precision as displayed. It makes the problem disappear by permanently overwriting every stored value in the workbook with its displayed value.
It is not a rounding tool. It is a data shredder with a friendly name, it applies to the whole workbook, and you cannot undo it by switching it back off. The digits are gone.
Try it yourself, it takes a minute
Open a blank sheet. Put 10.004 in A1, A2 and A3. Put =SUM(A1:A3) in A4. Format the lot to two decimals. Watch the column say 10.00 three times and the total say 30.01.
Then put =ROUND(A1, 2) in B1, fill it down, sum column B, and watch it settle.
Six tabs built on this lesson: the demonstration, fixing it with ROUND, the second argument including negative digits, choosing between the eight rounding functions, and a 12 line supplier invoice worked out both ways. Yellow cells are yours to fill in, each one marks itself as you go, and every answer is on the last tab.
Download the practice workbook (Excel, 33 KB)
Download the one page cheat sheet (PDF, 142 KB)
Watch it
Round Off in Microsoft Excel
Ever realised the figures are not adding up? Check your rounding
Back to the Content Hub
Still got a cent that will not go away?
Send me the sheet.. it might be the next lesson.
Get in Touch