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 is`0`

.

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 is`0`

.

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 zero`1`

– 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 is`0`

.

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 zero`1`

– 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.