Excel’s Round () function rounds a number to a specified number of digits. It might seem like the choice for your rounding needs but there are NINE more rounding functions that might be better and even that’s not enough to cover all rounding needs.
Round () basics
Round() is pretty simple, give it a value and the number of digits precision you want.
= ROUND(number, num_digits)
Number – refers to the number you want to round.
Num_digits – refers to the number of digits you want to round to.
- If num_digits:
- is greater than 0 (zero) – the number is rounded to the specified number of decimal places.
- is 0 – the number is rounded to the nearest integer … the same as Int()
- is less than 0 – the number is rounded to the left of the decimal point.
Note: Round() works fine for most purposes but it’s NOT completely accurate, see Why Excel’s Round() function is wrong
Round () In Action
Below is an example of a positive number in Excel.
ROUND (100.56879,2) returns the value 100.57 (nearest 2 decimal places),
ROUND(99.99,1) returns value of 100.0 (nearest 1 decimal place) and
ROUND(99.9,0) (nearest integer) returns the value of 100.
Below is an example of Round() with a negative number in Excel.
ROUND (-100.56879,2) returns the value -100.57,
ROUND (-99.9999,-1) and ROUND(-99.99,0) both return -100.
More rounding functions in Excel
Sometimes you need more than simple ‘nearest’ rounding. Over time, Excel has added cleverer rounding options
RoundUp() and RoundDown() control the direction of rounding.
MRound() lets you choose the multiple for rounding, e.g to the nearest 5 .
Ceiling() and Floor() are like MRound but you can also choose the direction of rounding, up or down.
ROUNDUP ()
= ROUNDUP(number, num_digits)
Like the Round() function, except you’ll always round up (strictly speaking, away from zero).
ROUNDDOWN ()
= ROUNDDOWN(number, num_digits)
Like the Round() function, except you’ll always round down (towards zero).
MROUND ()
Mround() lets you round to a nearest specific multiple and is so useful, we’ve devoted a separate article to it.
Ceiling() and Floor()
Yet more rounding options with Ceiling() or Floor() which work like MRound() but always round up (Ceiling) or down (Floor).
Even() and Odd()
Even() and Odd() also do rounding – to either the next even or odd integer.
Int() and Trunc()
Int() and Trunc() both remove the fractional part of a number, leaving an integer only but are slightly different, see The important difference between Int() and Trunc() in Excel
Rounding to the more accurate ISO standard
Incredibly, all ten Excel rounding functions are not enough. None of them comply with the international standard (ISO) for more accurate rounding of numbers.
See Why Excel’s Round() function is wrong and More accurate Excel rounding to the international specification
All about Ceiling() and Floor() Excel rounding options
Why Excel’s Round() function is wrong
All about MROUND() in Excel
The important difference between Int() and Trunc() in Excel
Discover all the Function Key goodies for PowerPoint
