It’s common practice in Excel to format decimal places to get the desired rounding of numbers. But the problem with this approach is it can display totals that don’t appear to add up correctly.
Take the screen shot below that shows 2+2+3=8. That’s because the underlying numbers are actually 2.4+2.4+3.2.
The solution is to use Excel’s ROUND, ROUNDUP or ROUNDDOWN formulas.
- ROUND rounds a number to a specified number of digits
- ROUNDUP rounds a number up, away from zero
- ROUNDDOWN rounds a number down, towards zero
Let’s take the number 2.4 and round it to no decimal places as an example.
Using ROUNDUP you’ll get 3.
Using ROUNDDOWN you’ll get 2.
Using ROUND you’ll also get 2. ROUND will round down anything under 5, and round up anything 5 and over.
How to enter a ROUND formula
The ROUND, ROUNDUP, ROUNDDOWN formulas can be applied to a cell, combined with other formulas or even contain their own calculation.
Let’s take the ROUND formula on its own first.
The Excel sytax is
In English it means:
=ROUND(cell reference (e.g. C2) or calculation (e.g. 5.3+2), the number of decimal places you want)
In the image below we can see our ROUND formula in the formula bar as
Note: You can see that even though I’ve told Excel to round my number to zero decimal places in cell G2 it’s still displaying the number as 2.0. This is simply because the cell formatting is to one decimal place.
Let’s say I wanted to round the number 2.489 to 2 decimal places. My formulas would read:
=ROUND(2.489,2) would give you 2.49
=ROUNDUP(2.489,2) would give you 2.49
=ROUNDDOWN(2.489,2) would give you 2.48
Ok, so that’s pretty easy. Let’s look at how we’d use a ROUND formula with another formula.
ROUND with another formula
ROUND a SUM to no decimal places =ROUND(SUM(C1:C10),0)
You can see the ROUND formula is wrapped around the SUM formula.
ROUNDUP with IF =ROUNDUP(IF(C10>=450,C10*9%,""),0)
ROUNDDOWN with AVERAGE =ROUNDDOWN(AVERAGE(C1:C10),0)
You’re not limited to these examples above, ROUND can be used with almost any formula.
ROUND to the nearest 5 cents
What say you priced products in 5 cent increments, but you found that when you marked up the cost price you often ended up with an amount that didn’t end in a 5 or a whole number. Take the example below where the selling price calculates at $9.96, but you have to price it at $9.95 or $10.00.
You can use the ROUND formulas here too.
By dividing the calculation by 5 cents (0.05) and rounding it to 0 decimal places, you can then multiply it by 5 cents (0.05) to get the correct amount.
If you wanted to round to the nearest 50 cents you would just replace the two instances of 0.05 with 0.50.
Alternatively you could use the CEILING or FLOOR formulas to do this, but that’s a lesson for another day.
Did you find this useful, or did it open a can of worms? Let us know by leaving a comment.
Share the knowledge with your friends and colleagues on Twitter, Facebook etc. using the shortcuts below.