Round Numbers to the Nearest 5 or 10 in Google Spreadsheets

Use this easy function to make rounding automatic

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 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 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. Thus, using this function to round data affects the results of calculations.

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. 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 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 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 rows 8 and 9 of the image — 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 Spreadsheets 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 on cell C1 in the worksheet to make it the active cell. This is where the results of the MROUND function will be displayed.

    Google Sheets active cell
  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.

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

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.

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

    Google Sheets MROUND by .5
  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 click cell C1, the complete function = MROUND (A1, 0.5) appears in the formula bar above the worksheet.

    Google Sheets MROUND result