Skip to content

Excel’s Round () and nine other rounding functions 

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

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.