Table of Contents
Rounding to the nearest 5 or 10 in Google Sheets can be done by using the round function. The round function takes two arguments, the number to be rounded and the number of decimal places to which it should be rounded. To round up or down to the nearest 5 or 10, you can use the formula =round(number, number of decimal places) and set the decimal places to 0. This will round the number to the nearest whole number. For example, =round(12.2,0) will round 12.2 to 12.
You can use the following formulas to round values to the nearest 5 or 10 in Google Sheets:
Method 1: Round to Nearest 5
#round value in cell A1 to nearest 5 =MROUND(A1, 5) #round value in cell A1 up to nearest 5 =CEILING(A1, 5) #round value in cell A1 down to nearest 5 =FLOOR(A1, 5)
Method 2: Round to Nearest 10
#round value in cell A1 to nearest 10 =MROUND(A1, 10) #round value in cell A1 up to nearest 10 =CEILING(A1, 10) #round value in cell A1 down to nearest 10 =FLOOR(A1, 10)
The following examples show how to use each formula in practice.
Example 1: Rounding to Nearest 5 in Google Sheets
The following screenshot shows how to round each value in column A to the nearest 5:
The formulas in column B round the values to the nearest 5.
The formulas in column C round the values up to the nearest 5.
And the formulas in column D round the values down to the nearest 5.
Example 2: Rounding to Nearest 10 in Google Sheets
The following screenshot shows how to round each value in column A to the nearest 10:
The formulas in column B round the values to the nearest 10.
The formulas in column C round the values up to the nearest 10.