Excel's Rounding Functions - ROUND, MROUND, ROUNDUP, ROUNDDOWN


Descriptions of Rounding Functions
Function   Description 
  ROUND   Rounds a number to a specified number of digits
  ROUNDUP   Always rounds a number up
  ROUNDDOWN    Always rounds a number down
  MROUND   Rounds a value to a desired multiple

ROUND

The ROUND function can round a number up or down to a specified number of digits.

The Syntax for the ROUND function is:
   ROUND(number, number_of_digits)

Argument Descriptions:
      Number is the number you want to round.
      Number_of_digits is the number of digits to which you want to round the number.
                    A negative value rounds to the left of the decimal point.
                    A Zero value rounds to the nearest integer.

The number and number_of_digits arguments are both required.
If the number_of_digits is greater than 0 the number will be rounded to the specified number of decimal positions.  In the example below, the number 585.758 will be rounded to the second position to the right of the decimal.
        =ROUND(585.758,2)     Result: 585.76

If the number_of_digits is 0 then the value is rounded to the nearest whole number.
        =ROUND(275.81,0)      Result: 276

The number can be rounded up or down. If the digit to be rounded is 5 or more it is rounded up. If it is 4 or less it is rounded down.
        =ROUND(7.5,0)    Result: 8
        =ROUND(7.4,0)    Result: 7

If the number_of_digits is a negative number the number is rounded to the left of the decimal.  If the number_of_digits is a -1 the result will be rounded to the nearest tens place.  If the number_of_digits is a -2 the result will be rounded to the nearest hundreds place, etc.
      =ROUND(314.76,-1)     Result: 310
      =ROUND(314.76,-2)     Result: 300

ROUNDUP

The ROUND function always rounds a value of 5 and above up and a value of less than 5 down, but the ROUNDUP function always rounds the value from 0 no matter what the value is.

The Syntax for the ROUNDUP function is:
   ROUNDUP(number, number_of_digits)

Argument Descriptions:
      Number is the number you want to round.
      Number_of_digits is the number of digits to which you want to round the number.
                     A negative value rounds to the left of the decimal point.
                     A Zero value rounds to the nearest integer.

Results from the ROUNDUP function
Formula    Result 
  =ROUNDUP(22.11,0)  23 - Rounds up to 0 decimal places
 =ROUNDUP(23.85,1)  23.9
 =ROUNDUP(22.11,1)  22.2
 =ROUNDUP(-35.853, 2)    -35.86
=ROUNDUP(23.85,-1)  30 - Rounds up to the tens place  
 =ROUNDUP(23.85,-2)  100 - Rounds up to the hundreds place

ROUNDDOWN

The ROUND function always rounds a value of 5 and above up and a value of less than 5 down, but the ROUNDDOWN function always rounds the value towards 0 no matter what the value is.

The Syntax for the ROUNDDOWN function is:
   ROUNDDOWN(number, number_of_digits)

Argument Descriptions:
      Number is the number you want to round.
      Number_of_digits is the number of digits to which you want to round the number.
                     A negative value rounds to the left of the decimal point.
                     A Zero value rounds to the nearest integer.

Results from the ROUNDUP function
Formula    Result 
  =ROUNDDOWN(22.11,0)  22 - Rounds down to 0 decimal places
 =ROUNDDOWN(23.85,1)  23.8
 =ROUNDDOWN(22.11,1)   22.1
 =ROUNDDOWN(-35.853, 2)    -35.85
 =ROUNDDOWN(23.85,-1)  20 - Rounds down to the tens place  
 =ROUNDDOWN(23.85,-2)    0 - Rounds down to the hundreds place

MROUND

The MROUND function rounds a value up or down to the nearest multiple you provide.

The Syntax for the MROUND function is:
  MROUND(number, multiple)

Argument Descriptions:
      Number is the number you want to round.
      Multiple is the multiple to which you want to round the number

The two arguments Number and Multiple must both have the same sign otherwise you will get a #NUM! error.

To determine if the number is to be rounded up or down, divide the number by the multiple.  If the remainder is less than half the value of the multiple then the number is rounded down otherwise the number is rounded up.
The function below rounds 92 to the nearest 10s place.
=MROUND(92,10)
The result of the function is 90
Since the remainder of 92 divided by 10 is 2 and 2 is less than half of the multiple value of 10 the value is rounded down to the nearest 10s place which is 90.

The function below rounds 95 to the nearest 10s place.
=MROUND(95,10)
The result of the function is 100.
SSince the remainder of 95 divided by 10 is 5 and 5 is equal to half of the multiple value of 10 the value is rounded up to the nearest multiple of 10 which for this example is 100.

Results from the MROUND function
Function    Result  Description   
 =MROUND(3715,100)  3700  The closest multiple of 100 is 3700
 =MROUND(3715,1000)    4000  The closest multiple of 1000 is 4000  
 =MROUND(31.756,.1)  31.8  The closest multiple of .1 is .8
 =MROUND(31.746,.1)  31.7  The closest multiple of .1 is .7