Rounding in Excel: ROUND, ROUNDUP and ROUNDDOWN
A number formatted to two decimals is not rounded; the full value is still there. When to round with a function, and how ROUND, ROUNDUP, ROUNDDOWN differ.
To round a number in Excel, use a function: =ROUND(A1,2) rounds
A1 to 2 decimal places. Formatting a cell to 2 decimals only changes what you see.
ROUNDUP and ROUNDDOWN use the same shape. ROUNDUP always rounds away from zero. ROUNDDOWN always rounds towards zero.
Formatting is not rounding
This sheet adds 20% VAT to 3 prices. C3 holds =B3*0.2, filled
down. Column C is formatted to show 2 decimals. C6 holds
=SUM(C3:C5).
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Bike shop | |||
| 2 | Item | Price | VAT | Rounded VAT |
| 3 | Chain | 3.49 | 0.70 | 0.70 |
| 4 | Bell | 2.49 | 0.50 | 0.50 |
| 5 | Light | 1.49 | 0.30 | 0.30 |
| 6 | Total | 1.49 | 1.50 |
Add up column C in your head: 0.70 plus 0.50 plus 0.30 is 1.50. But C6 shows 1.49. Excel did not make a mistake.
The cells really hold 0.698, 0.498 and 0.298. The format hides the third decimal. SUM adds the real values, and their total is 1.494. Shown with 2 decimals, that is 1.49.
Fix: round the value, not the format. In D3, type
=ROUND(B3*0.2,2) and fill it down.
Now D3 really holds 0.7. The total in D6 is 1.5, and it matches the lines above it.
The Increase Decimal and Decrease Decimal buttons on the Home tab are format too. They never change the value in the cell.
Round each line, or only the total?
The two totals on this sheet are 1.49 and 1.50. Both are correct answers to different questions.
-
=ROUND(SUM(C3:C5),2)rounds once, at the end. It gives 1.49. -
=SUM(D3:D5)adds lines that are already rounded. It gives 1.5.
An invoice usually shows each line, so the lines must add up to the total. Check which method your invoice or tax rules expect. Then use that one method everywhere in the file.
ROUND, ROUNDUP and ROUNDDOWN side by side
All 3 functions take a number, then how many decimal places to keep. Here are 5 numbers, each rounded to 0 decimal places.
| Number | ROUND | ROUNDUP | ROUNDDOWN |
|---|---|---|---|
| 7.2 | 7 | 8 | 7 |
| 7.5 | 8 | 8 | 7 |
| 7.8 | 8 | 8 | 7 |
| -7.2 | -7 | -8 | -7 |
| -7.5 | -8 | -8 | -7 |
- ROUND goes to the nearest whole number. A half goes away from zero, so 7.5 gives 8 and -7.5 gives -8.
- ROUNDUP always moves away from zero. For -7.2 that means -8, not -7.
- ROUNDDOWN always moves towards zero. It just cuts off the extra digits.
A number that is already whole does not change. =ROUNDUP(7,0)
gives 7.
When nearest is the wrong answer
ROUND is right for money and for averages. The other two are for things that come in whole units.
Boxes to order. A box holds 6 items and you need 43.
=43/6 is about 7.17. =ROUND(43/6,0)
gives 7, and 7 boxes hold only 42. =ROUNDUP(43/6,0) gives 8.
Full litres in stock. You have 3.8 litres and sell whole litres only.
=ROUND(3.8,0) gives 4, and you cannot sell the fourth.
=ROUNDDOWN(3.8,0) gives 3.
The second argument
The second argument says where to round. It can be positive, zero or negative. These all start from 1234.5.
=ROUND(1234.5,0)gives 1235, a whole number.=ROUND(1234.5,-2)gives 1200, the nearest hundred.=ROUND(1234.5,-3)gives 1000, the nearest thousand.
Negative numbers are useful for a report in round numbers. The second argument is not optional.
=ROUND(C3) is not a formula Excel will accept. It says there are
too few arguments.
Rounding an average
Averages often have many decimals. Scores of 7, 8 and 8 in F1:F3 average 7.666..., and the 6s never stop.
=ROUND(AVERAGE(F1:F3),1) gives 7.7.
Put AVERAGE inside ROUND. Excel works out the average first, then rounds the result.
Questions people ask
What is the difference between INT and ROUNDDOWN?
On positive numbers, nothing. On negative numbers, INT goes down to the lower number.
=INT(-7.2) gives -8. =ROUNDDOWN(-7.2,0) gives -7.
Can I use TEXT to round?
=TEXT(C3,"0.00") shows 0.70, but the result is text. SUM skips
text in a range, so a total of those cells comes out as 0. Use ROUND when you need a number.
Should I turn on "Set precision as displayed"?
Be careful with it. The option is under File, Options, Advanced. It changes stored numbers to match what their format shows. You cannot get the lost decimals back. ROUND does the same job, one cell at a time, and you can see it in the formula.
Why does Excel round 2.5 up to 3?
Excel's ROUND always moves a half away from zero. =ROUND(2.5,0)
gives 3 and =ROUND(-2.5,0) gives -3. Some programming languages
round a half to the even number instead. Excel's ROUND does not.