Excel MROUND Function

How to round down to the nearest 5 cents in Excel

screenshot/Microsoft/Ted French

The MROUND function makes it easy to round up or down in Excel. Let's learn how to round down to the nearest 5 cents in an Excel worksheet when making financial calculations to avoid having to deal with pennies. The instructions below work for all modern versions of Excel including 2016, 2013, and 2010 in addition to Excel for Mac and Excel for Android.

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

The MROUND Function's Syntax and Arguments

Syntax refers to the layout of the function including the function's name, brackets, and arguments. The syntax for the MROUND function is:


=MROUND(Number, Multiple)

The arguments for the function are:

Number: (required) The number to be rounded up or down to the nearest integer. 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) The function rounds the number argument up or down to the nearest multiple of this value.

Here are some points to note regarding the MROUND function's arguments:

  • The number and multiple arguments must be both positive or both 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 in the cell as shown in row four in the example image above.
  • If the multiple argument is set to zero (0), the function returns a value of zero in the cell as shown in row seven in the image above.

MROUND Function Examples

For the first six examples in the image above, 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 results are displayed in column C, and the formula producing the results are in column D.

Rounding Up or Down

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

  • If this 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) are used to demonstrate how the function handles rounding up or down.

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

Example 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 MROUND 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. Referring again to the examples in the image above, let's say you wanted to enter the function =MROUND(A2,0.05) into cell C2 using the dialog box. You'd take the following steps:

  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 list
  4. Select MROUND in the list to open the function's dialog box
  5. In the dialog box, select the Number line
  6. Select cell A2 in the worksheet to enter this cell reference as the number argument
  7. In the dialog box, select the Multiple line
  8. Type in 0.05 so that the number in A2 will be rounded up or down to the nearest multiple of 5 cents
  9. Select OK to close the dialog box and 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. When you select cell C2, the complete function, = MROUND ( A2 , 0.05 ), will appear in the formula bar above the worksheet.