How to Use Excel's MROUND Function

The MROUND function round numbers up or down in Excel to the nearest multiple. For example, when making financial calculations in a worksheet, you can round an amount to the nearest 5 cents avoid dealing with pennies.

Unlike formatting options that allow you to change the number of decimal places displayed without changing the value in a cell, the MROUND function alters a cell's data. Using this function to round data will, therefore, affect the results of formulas.

MROUND Function Syntax and Arguments

The layout of a function is called its syntax which includes the function's name, brackets, and arguments. The syntax for the MROUND function is:

=MROUND(Number,Multiple)

Number (required) is the value you want to round up or down to the nearest multiple. This argument can contain the actual data for rounding, or it can be a cell reference to the location of the data in the worksheet.

Multiple (required) is the multiple to which you want to round the number.

Some things to note about the MROUND function's arguments:

• The number and multiple arguments must have the same sign. That is, if the number is positive, then the multiple must be positive. If the number is negative, the multiple must also be negative. If not, the function returns a #NUM! error in the cell.
• If the number and multiple arguments are both negative, the function returns a negative number.
• If the multiple argument is zero (0), the function returns a value of zero.

MROUND Function Examples

For the first six examples in the image below, the number 4.54 is rounded up or down by the MROUND function using a variety of values for the factor argument such as 0.05, 0.10, 5.0, 0, and 10.0. The formula is in column B, the results are in column C, and a description of each result is in column D.

How the function determines whether to round the last remaining digit (the rounding digit) up or down depends on the result of dividing the number argument by the multiple argument. Therefore:

• If the result is greater than or equal to half the value of the multiple argument, the function rounds the last digit up (away from zero).
• If this result is less than half the value of the multiple argument, the function rounds the last digit down (towards zero).

The last two examples (in row 8 and 9 of the image) demonstrate how the function handles rounding up or down.

• In row 8, since the multiple argument is a single digit integer (5), the 2 becomes the rounding digit in the number 12.50 value in cell A8. Since 2.5 (12.5/5) is equal to half the value of the multiple argument (5), the function rounds the result up to 15, which is the nearest multiple of 5 greater than 12.50.
• In row 9, since 2.49 (12.49/5) is less than half the value of the multiple argument (5), the function rounds the result down to 10, which is the nearest multiple of 5 less than 12.49.

Using Excel's MROUND Function

Options for entering the function and its arguments include:

• Typing the complete function into a worksheet cell
• Selecting the function and its arguments using the Function Dialog Box

Many people find it easier to use the dialog box to enter a function's arguments as it takes care of the function's syntax.

=MROUND(A2,0.05)

Take the following steps to enter the function in the image above into cell C2 using the Function Dialog Box:

1. Select cell C2 to make it the active cell.

2. Select the Formulas tab of the ribbon.

3. Select on the Math & Trig icon to open the function drop-down.

   

4. Select MROUND in the list to open the Function Dialog Box.

5. Select the Number line.

6. Select cell A2 in the worksheet to enter this cell reference as the number argument.

7. Select the Multiple line.

8. Type in 0.05 so that the number in cell A2 will be rounded up or down to the nearest multiple of 5 cents.

9. Select Done to return to the worksheet. The value 4.55 should appear in cell C2 since it is the nearest multiple of 0.05 larger than 4.54.

10. When you select cell C2, the complete function will appear in the formula bar above the worksheet.