There are many ways to round numbers in a spreadsheet. However, you must carefully choose the correct method as many choices exist. We will start with the easiest way, simply changing the cell format to show fewer significant digits.
Grab a copy of the template to work with these examples in your own spreadsheet.
Contents
Rounding with Number Formatting
Changing the number format changes how Google Sheets displays a number in a cell. It does not change the actual value of the number. For example, if you have the number 1.5 in a cell and change the number format to display no decimal places, the number will be displayed as 2. However, the actual value of the number in the cell is still 1.5.
You can safely round numbers for display purposes, even if you plan to use them in calculations later. But if you use a rounded number in a calculation, the actual value used will differ from what’s displayed.
This image shows that 2 + 2 = 3. Is that right? Well, sort of. Next, let’s look at how to change numbers instead of just changing their displayed values.
Rounding with Functions
Depending on your needs, there are three techniques for rounding numbers: regular rounding, rounding up, and rounding down. Let’s start with regular rounding.
Regular Rounding (Up or Down)
Regular rounding approximates a value by moving it slightly up OR down. Google Sheets provides two ways to do this. You can use the ROUND function to apply standard rounding to a specified number of decimal places, or MROUND to round to a specified multiple. We’ll look at the ROUND function first.
ROUND Function
=ROUND(value, [places])
value– The number that you want to round.places– Optional. The number of decimal places to round to. If you don’t specify a place value, the default is0.
ROUND applies standard rounding principles, which we all learned in math class. It rounds everything below five down and everything five and above up. Let’s look at an example.
ROUND Function Example 1
Round 123.4567 to two decimal places:
=ROUND(123.4567, 2)
This function will decrease the decimals from four to two and return the value of 123.46.
ROUND Function Example 2
Round 123.4567 to the nearest integer:
=ROUND(123.4567, 0)
This example removes four levels of precision from 123.4567 by changing it to 123.
ROUND Function Example 3
The function accepts different inputs for the places argument to control how many decimal places Google Sheets shows. Using negative numbers removes significant digits to the left of the decimal place.
You may want to round up or down to a multiple other than ten. Let’s look at that next.
MROUND Function
=MROUND(value, factor)
value – The number for the function to round
factor – The multiple to round with
MROUND Function Example 1
The MROUND function rounds a number to a specified factor instead of a place. For example, you may round currency to the nearest nickel. Let’s look at an example.
Round $1.21 to the nearest nickel:
=MROUND(1.21, .05)
This rounding example would change $1.21 to $1.20.
MROUND Function Example 2
The factor argument changes the multiple to which Google Sheets rounds the values. You can round negative or positive numbers. Here is a table showing both with different factors.
The positive and negative values move toward the same absolute values.
These two functions cover the options for rounding up or down. Now let’s look at rounding only in one direction.
Rounding Up
There are several options for rounding numbers up in Google Sheets. Let’s break them down and discuss each function.
ROUNDUP Function
=ROUNDUP(value, [places])
value– The number to round.places– Optional. The number of decimal places to round to. If you don’t specify a place value, the default is0.
The ROUNDUP function in Google Sheets rounds a number up to a specified number of decimal places.
ROUNDUP Example 1
ROUNDUP is simple to understand compared to the other functions in this tutorial. This table illustrates its usage.
Rows 2 and 3 have the same result even though the places arguments (3 and 2) are different. This difference is because Google Sheets only shows decimal places to the last significant digit unless you apply formatting to force insignificant digits to display.
Google Sheets rounds the results in rows 4 and 5 to 1 and 0 decimal places, respectively. The 123.5 and the 124 are the new values, with the decimal values discarded.
The negative values for places in rows 6 through 8 are removing significant digits. These results show that the ROUNDUP function can remove more than decimals if you use a negative number for the places argument.
ROUNDUP Example 2
This function also rounds negative numbers. Let’s take the previous example and change the sign to negative.
=ROUNDUP(-123.4567, 0)
This formula returns the value -124. If you want more control over how the function rounds negative numbers, consider using the CELING.MATH or FLOOR.MATH.
Note: Google Sheets provides the CEILING function with CEILING.MATH and CEILING.PRECISE for more control over negative numbers.
CEILING.MATH Function
=CEILING.MATH(number, [significance], [mode])
number – The number for the function to round
significance – Optional. The multiple to round up to. The default value is 1.
mode – Optional. The rounding direction for negative numbers. This argument has no impact on positive numbers.
0– Round toward zero1– Round away from zero
The CEILING.MATH function rounds a number up to the nearest multiple of a given significance while giving you control over how it rounds negative numbers. Let’s break this down.
CEILING.MATH Example 1
The CEILING.MATH function rounds a number up to a specified significance. For example, you may round times up to the next half-hour. Let’s look at an example.
Round 1:20 PM up to the nearest half-hour:
=CEILING.MATH("1:20 PM", ":30")
This rounding example changes 1:20 PM to 1:30 PM.
CEILING.MATH Example 2
Unlike CEILING.PRECISE, the CEILING.MATH function lets you control which direction it rounds negative numbers. In this image, we use both modes to switch between toward and away from zero.
You can see the 0 mode rounding negative numbers toward zero while a non-zero mode rounds numbers away from zero.
CEILING.PRECISE Function
=CEILING.PRECISE(number, [significance])
number – The number for the function to round
significance – Optional. The multiple to round up to. The default value is 1.
The CEILING.PRECISE function is similar to the CEILING.MATH function, except it does not have a mode argument to control the rounding of negative numbers. Use this function if you want something simpler than CEILING.MATH.
Rounding Down
Rounding down offers even more options than rounding up. Let’s explore them so you can find the best one for your spreadsheet. We’ll start with the most straightforward function, which rounds down by places.
ROUNDDOWN Function
=ROUNDDOWN(value, [places])
value– The number that you want to round.places– Optional. The number of decimal places to round to. If you don’t specify a place value, the default is0.
The ROUNDDOWN function in Google Sheets rounds a number down to a specified number of decimal places.
ROUNDOWN Example 1
Let’s try different values for the places argument using 456.78 every time. We’ll see various levels of rounding as a result.
Rows 2 and 3 have the same result even though the places arguments (3 and 2) are different. This difference is because Google Sheets only shows decimal places to the last significant digit unless you apply formatting to force Google Sheets to show insignificant digits. Therefore, the 456.78 in row 2 is only shown as 456.78 since the ending 0 does not impact the number’s value.
The function removed all of the significant digits in the last row. Be careful when using a negative number for the places argument as the results can differ by orders of magnitude.
ROUNDOWN Example 2
This function also rounds negative numbers. Let’s change the value‘s sign to negative.
=ROUNDDOWN(-123.4567, 0)
This formula returns the value -123, which is a different result than the INT function provides. Read more about the difference between INT and ROUND in Google Sheets.
INT Function
=INT(value)
value – The number that you want to round down.
The INT function in Google Sheets rounds a number down to the nearest integer. This function can simplify large numbers or convert decimal values to integers.
INT Example 1 – Round a Decimal to a Whole Number
To round down the number 12.345 to 12, you would use the following formula:
=INT(12.345)
This simple formula returns 12.
INT Example 2 – Negative Numbers
Next, let’s look at using INT on a negative number. We’ll use 12.345 again but switch the sign to -12.345.
=INT(-12.345)
The result is -13. It is essential to realize this is a different result than rounding a number with the ROUND function. See a comparison of INT and ROUND for more.
FLOOR.MATH Function
=FLOOR.MATH(number, [significance], [mode])
number – The number for the function to round
significance – Optional. The multiple to round down to. The default value is 1.
mode – Optional. The rounding direction for negative numbers. This has no impact on positive numbers.
0– Round away from zero1– Round toward zero
The FLOOR.MATH function rounds a number down to the nearest multiple of a given significance, while giving you control over how it rounds negative numbers. Let’s take a look.
FLOOR.MATH Example 1
The FLOOR.MATH function rounds a number down to a specified significance. For example, you may round a price down to the nearest nickel. Let’s look at an example.
Round $1.23 down to the nearest nickel:
=FLOOR.MATH(1.23, .05)
This rounding example changes $1.23 to $1.20. Notice you need to add the currency symbol with formatting; it is not part of the formula.
FLOOR.MATH Example 2
Unlike FLOOR.PRECISE, the FLOOR.MATH function lets you control which direction it rounds negative numbers. In this image, we use both modes to switch between toward and away from zero.
You can see the 0 mode rounding negative numbers away from zero while a non-zero mode rounds numbers toward zero.
FLOOR.PRECISE Function
=FLOOR.PRECISE(number, [significance])
number – The number for the function to round
significance – Optional. The multiple to round down to. The default value is 1.
The FLOOR.PRECISE function is similar to the FLOOR.MATH function, except it does not have a mode argument to control the rounding of negative numbers. Use this function if you want something simpler than FLOOR.MATH.
TRUNC Function
=TRUNC(value, [places])
value – The number that you want to truncate.
places – An optional argument specifying the number of digits. The default is 0.
The TRUNC function removes unwanted digits from a number. The TRUNC function is similar to the INT function, but TRUNC accepts a place argument and treats negative numbers differently.
TRUNC Function – Example 1
The following formula changes the number 12.345 to 12:
=TRUNC(12.345)
TRUNC does not follow standard rounding rules. It simply removes numbers.
TRUNC Function – Example 2
Next, let’s look at using TRUNC on a negative number. We’ll switch the sign to -12.345 and keep the places argument at the default of 0.
=TRUNC(-12.345)
The result is -12. It is essential to realize this is a different result than rounding a number with the INT function, which would have returned -13.
Conclusion
Google Sheets offers a variety of rounding options. This tutorial can help you decide which option is best for rounding up or down. When done correctly, rounding makes spreadsheets easier and faster for users to understand.
YouTube Video
