Google Sheets makes it easy to round numbers. It features several functions that can accommodate a wide-range of rounding.
Whether you need to round to the nearest whole number, the second decimal point, hundreds, or by a factor of three, there’s a rounding function built-in to Google Sheets to help.
In this tutorial, I’ll cover how to round numbers in Google Sheets using various rounding functions (such as ROUND, ROUNDUP, ROUNDDOWN, and MROUND).
Rounding Numbers to the Nearest Whole Number (Integer) in Google Sheets
You can round any numerical value to the nearest whole number by using the Google Sheets function =ROUND. The feature uses a very straightforward syntax, with only the value inside the parentheses:
If you filled in the function with the number “2.6” as the value it looks like:
A round function with the number “2.6” inside will round to the closest whole number, so it will return the value 3. The function also works with using a cell reference, like “A2,” instead of a number.
The following example shows three numbers, the =ROUND formula referencing their cells, and the rounded result:
The ROUND formula will round up or down to the closest integer, regardless of how many digits follow the decimal.
Rounding Numbers to the Nearest Decimal Place
Rounding to whole numbers will only get you so far in Google Sheets: eventually, you’re going to need to round to a decimal point.
You can modify the ROUND function to use a specified number of decimal places through a parameter.
The syntax looks like this:
So if you want to round to one decimal place, you’d enter the value “1” for places:
If we applied this formula to the number “9.134” it would return “9.1” in the cell. If you want to return to two decimal places, you’d enter “2” instead, and so forth for any number of decimal places.
One of the interesting things this function can do is it can round to 10s, 100s, and so on if you use negative place value. For example:
This function rounds to the nearest 10s, which would round down and return the number 10.
The following example demonstrates how Google Sheets rounds to decimal points with both positive and negative places settings:
Forcing rounding up and rounding down
Sometimes you may want Google Sheets to always round up or round down with your results. Fortunately, Sheets features two intuitively named functions called =ROUNDUP and =ROUNDDOWN that handle this.
The rounding up and rounding down functions use the following syntax, similar to the standard =ROUND:
If we were to apply the =ROUNDUP function to the value “2.676” with a place setting of “0” it would round the result up and return “3.”
If we were to apply the =ROUNDDOWN function to the value “2.676” with a place setting of “0” it would round the result up and return “2.”
The following example shows how Google Sheets handles ROUNDUP and ROUNDDOWN against a range of numbers with different places settings:
Rounding to factors
You can take your rounding capabilities in Google Sheets to a higher level using the multiple round functions. These functions round to the closest multiple of a given factor instead of a decimal position.
The MROUND function uses this syntax:
It will round to the closest multiple of the factor. So if you enter the value as “14” and the factor as “5” the function will round to the nearest multiple of 5, which is 15.
There are two additional multiple rounding functions called =CEILING and =FLOOR which perform factor-based rounding, but always round up or down respectively.
Both CEILING and FLOOR use the same syntax style as the MROUND function:
The following example shows the formulas and results for rounding the numbers “7” and “8” against a factor of “3” for the multiple rounding functions:
Note: The multiple rounding functions all work with factors with decimals
ROUNDING Numbers from Formulas Results
The ROUND formula is useful for returning a result but requires referencing a completed calculation and a new cell if used on its own.
Fortunately, Google Sheets lets you use ROUND as a wrapper to return a rounded result.
For example, let’s say we want to add the values in cells “A2” and “B2,” but we want to round the result to the nearest whole number.
To accomplish this, we can calculate the value of “A2” plus “B2” with the formula “=SUM(A2:B2).” We can wrap that formula inside =ROUND like this:
The following example demonstrates using the above formula to add “4.44” to “5.71” in cells A2 and B2 respectively, then round the result to the nearest whole number, which is “10.”
In this tutorial, I covered how to round numbers in Google Sheets using different functions.
The built-in rounding functions in Google Sheets are an extremely versatile set of tools to use on your workbooks.
I hope you found this tutorial helpful!
Other Google Sheets tutorials you may find useful:
- How To Calculate Weighted Average In Google Sheets (AVERAGE.WEIGHTED)
- How to Convert Text to Numbers In Google Sheets
- Calculate the Number of Days Between Two Dates in Google Sheets