Round Numbers to the Nearest 5 or 10 in Google Sheets

The Google Sheets' MROUND function makes it easy to round a number upward or downward to the nearest 5, 10, or another specified multiple. For example, you can use the function to round up or down the cost of items to the nearest five cents ($0.05) or 10 cents ($0.10) to avoid having to deal with pennies ($0.01) as change.

Unlike formatting options that allow you to change the number of decimal places displayed without changing the value in the cell, the MROUND function, like Google Sheets' other rounding functions, alters the value of the data. Thus, using this function to round data affects 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 MROUND function is:

= MROUND (value, factor)

The arguments for the function are:

• value (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.
• factor (required): The function rounds the value argument up or down to the nearest multiple of this value.

Points to note regarding the function's arguments:

• If the factor argument is omitted, a #N/A error displays in the cell containing the function.
• The factor and value arguments must have the same sign—either positive or negative. If not, the function returns a #NUM! error in the cell.
• If the factor and value arguments are both negative, the function returns a negative number in the cell, as shown in row 4 in the image above.
• If the factor argument is set to zero (0), the function returns a value of zero in the cell, as shown in row 7 in the image above.

MROUND Function Examples

For the first six numbers 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, in column D.

Rounding Up or Down

Whether the last remaining digit or integer (the rounding digit) is rounded up or down depends upon the value argument.

• If the rounding digit and all numbers to the right of it in the value argument are less than half the value of the factor argument, the function rounds down the last digit.
• If the rounding digit and all numbers to the right of it in the value argument are greater than or equal to half the value of the factor argument, the rounding digit is rounded up.

The last two examples demonstrate how the function handles rounding up or down.

• In row 8, because the factor argument is a single-digit integer, the 2 becomes the rounding digit in the 12.50 value in cell A8. Because 2.5 is equal to half the value of the factor 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, because 2.49 is less than half the value of the factor argument (5.00), the function rounds the result down to 10.00, which is the nearest multiple of 5.00 less than 12.49.

Entering the MROUND Function

Google Sheets does not use dialog boxes to enter a function's arguments, unlike Excel. Instead, it has an auto-suggest box that pops up as you type the name of the function into a cell. To see this in action:

1. Enter 4.54 into cell A1.

2. Click cell C1 in the worksheet to make it the active cell. This is where the results of the MROUND function will be displayed.

3. Type the equal sign (=) followed by MROUND. As you type, an auto-suggest box appears with the names of functions that begin with the letter M.

4. When MROUND appears in the box, select it to enter the function and an open round bracket into cell C1.

Enter a Function's Argument

To enter a function's argument:

1. Enter the arguments for the MROUND function after the open round bracket in cell C1. Type 0.5 to enter this number as the factor argument. It should appear as =MROUND(A1,0.5).

   Another way to enter the cell reference aside from typing it: Select cell A1 in the worksheet. Enter a comma to act as a separator between the function's arguments.

2. Press Enter to enter a closing parenthesis [ ) ] after the function's argument and to complete the function. The value 4.5 should appear in cell C1, which is the nearest multiple of 0.5 larger than 4.54.

3. When you select cell C1, the complete function = MROUND (A1, 0.5) appears in the formula bar above the worksheet.