Excel MROUND Function

01
of 01

Round Up or Down to the Nearest 5 or 10 in Excel

Rounding Numbers Up or Down to the Nearest 5 or 10 cents in Excel
Round Numbers Up or Down to the Nearest 5 or 10 cents in Excel. © Ted French

MROUND Function Overview

Excel's MROUND function makes it easy to round a number upwards or downwards to multiples of 5, 10, or any other specified value.

For example, the function can be used to round up or down the cost of items to the nearest:

  • five cents (0.05)
  • ten cents (0.10)

to avoid having to deal with pennies ( 0.01) as change.

Unlike formatting options that allow you change the number of decimal places displayed without actually changing the value in the cell, the MROUND function, like Excel's other rounding functions, does alter the value of the data.

Using this function to round data will, therefore, affect the results of calculations.

The MROUND Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the ROUNDDOWN 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

Multiple - (required) the function rounds the Number argument up or down to the nearest multiple of this value.

Points to note regarding the function's arguments are:

  • the Number and Multiple arguments must have the same sign - either positive or 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 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

In the image above, for the first six examples, 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 in column D.

Rounding Up or Down

According to the Excel help file, how the function determines whether to round the last remaining digit (the rounding digit) up or down depend on the remainder that results from dividing the Number argument by the Multiple argument.

  • 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:

  1. Typing the complete function: such as =MROUND(A2,0.05) into a worksheet cell
  2. 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 - such as the commas that act as separators between the arguments.

The steps below cover using the dialog box to enter the round function into cell C2 of the example above.

  1. Click on cell B2 to make it the active cell;
  2. Click on the Formulas tab of the ribbon;
  3. Click on the Math & Trig icon to open the function drop down list;
  4. Click on MROUND in the list to open the function's dialog box;
  5. In the dialog box, click on the Number line;
  6. Click on cell A2 in the worksheet to enter this cell reference as the Number argument;
  7. In the dialog box, click on the Multiple line;
  8. Type in 0.05 - the number in A2 will be rounded up or down to the nearest multiple of 5 cents;
  9. Click OK to close the dialog box and return to the worksheet;
  10. The value 4.55 should appear in cell B2, which is the nearest multiple of 0.05 larger than 4.54;
  11. When you click on cell C2 the complete function = MROUND ( A2 , 0.05 ) appears in the formula bar above the worksheet.
Format
mla apa chicago
Your Citation
French, Ted. "Excel MROUND Function." ThoughtCo, Aug. 9, 2017, thoughtco.com/round-numbers-up-or-down-nearest-5-or-10-p2-3123753. French, Ted. (2017, August 9). Excel MROUND Function. Retrieved from https://www.thoughtco.com/round-numbers-up-or-down-nearest-5-or-10-p2-3123753 French, Ted. "Excel MROUND Function." ThoughtCo. https://www.thoughtco.com/round-numbers-up-or-down-nearest-5-or-10-p2-3123753 (accessed January 19, 2018).