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

### 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

- 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.

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**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: such as
*=MROUND**(A2,0.05)*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 - 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.

- Click on cell B2 to make it the active cell;
- Click on the
*Formulas*tab of the ribbon; - Click on the
*Math & Trig*icon to open the function drop down list; - Click on MROUND in the list to open the function's dialog box;
- In the dialog box, click on the
*Number*line; - Click on cell A2 in the worksheet to enter this cell reference as the
*Number*argument; - In the dialog box, click on the
*Multiple*line; - Type in 0.05 - the number in A2 will be rounded up or down to the nearest multiple of 5 cents;
- Click OK to close the dialog box and return to the worksheet;
- The value 4.55 should appear in cell B2, which is the nearest multiple of 0.05 larger than 4.54;
- When you click on cell C2 the complete function
*= MROUND ( A2 , 0.05 )*appears in the formula bar above the worksheet.