Round Numbers to the Nearest 5 or 10 in Google Spreadsheets

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

Ted French

Google Spreadsheets' MROUND function makes it easy to round a number upwards or downwards to the nearest 5, 10, or another specified multiple.

For example, the function can be used to round up or down the cost of items to the nearest five cents (0.05) or 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 Google Spreadsheets' other rounding functions, does alter the value of the data.

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

Note: To round numbers up or down without specifying the amount of rounding, use the ROUNDUP or ROUNDDOWN functions instead.

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

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

  • if the factor argument is omitted, a #N/A error 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 four 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 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

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 the last digit down.
  • 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 — 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 factor 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 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, since 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 Spreadsheets does not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Enter the following data into cell A1: 4.54.
  2. Click on cell C2 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 the name of the function mround.
  4. As you type, the auto-suggest box appears with the names of functions that begin with the letter M.
  5. When the name MROUND appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell C2.

Entering the Function's Argument

The arguments for the MROUND function are entered after the open round bracket in cell C2.

  1. Click on cell A2 in the worksheet to enter this cell reference as the value argument.
  2. Enter a comma to act as a separator between the function's arguments.
  3. Type 0.05 to enter this number as the factor argument.
  4. Press the Enter key on the keyboard to enter a closing round bracket ) after the function's argument and to complete the function.
  5. The value 4.55 should appear in cell B2, which is the nearest multiple of 0.05 larger than 4.54.
  6. When you click on cell C2 the complete function = MROUND (A2, 0.05) appears in the formula bar above the worksheet.