# How To Round In Google Sheets

You can round numbers using functions in formulas or formatting.

The functions you can use are ROUND, MROUND, ROUNDDOWN, FLOOR, INT, ROUNDUP, CEILING, ODD, and EVEN.

If you want to round using formatting instead, select the cell/s you want to round and click these icons in the toolbar (left for fewer decimal places, right for more):

There's a couple of ways to round numbers in Google Sheets, each with pros and cons:

• Functions
• Pros:
• You can round to a specific number of decimal places.
• You can round to multiples of numbers (e.g. nearest 5, 10, 100, 1000)
• You can control the rounding throughout a multi-step calculation.
• Cons:
• Rounding in multi-step calculations can result in inaccuracies.
• Formatting
• Pros:
• You can round to a specific number of decimal places.
• The underlying numbers remain the same, they simply look rounded (no inaccuracies in multi-step calculations).
• Cons:
• You can only round to decimal places (not multiples).
• The results can sometimes look incorrect.

Knowing the pros and cons, click on the rounding method you want to use:

## Rounding Using Functions In Google Sheets

There are quite a few Google Sheets functions to help round numbers.

Click on the type of rounding you want:

Just remember to be careful when rounding using functions because of the potential issues.

### Round Either Up Or Down With Google Sheets Functions

For this you can use either the ROUND or MROUND functions:

• ROUND is for rounding to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• MROUND is for rounding up or down to the nearest multiple of the specified factor (e.g. nearest 5, 10, 1000, 0.5).

#### ROUND Function

=ROUND(value,[places])

The ROUND function takes a number (value) and rounds it up or down to the specified number of decimal places using specific rules:

• The digit to the right of the rounding point is considered with the number rounded up if it's >=5 and down if <=4.
• Negative numbers are rounded down towards 0 and rounded up away from 0.

[places] is an optional argument that is assumed to be 0 if left out.

Here's a few examples to show you how it works:

 A B C D E 1 value [places] Formula Output Rounding 2 3.1415 0 =ROUND(3.1415) 3 Down 3 3.1415 1 =ROUND(3.1415,1) 3.1 Down 4 3.1415 2 =ROUND(3.1415,2) 3.14 Down 5 3.1415 3 =ROUND(3.1415,3) 3.142 Up 6 -3.1415 3 =ROUND(-3.1415,3) -3.142 Up (away from 0) 7 3.1415 4 =ROUND(3.1415,4) 3.1415 None 8 3.1415 5 =ROUND(3.1415,5) 3.1415 None

Notice that when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

One cool trick to the ROUND function is that if you make [places] negative, it will round up or down to a specific number of places to the left of the decimal point:

 A B C D E 1 value [places] Formula Output Rounding 2 31415 0 =ROUND(31415) 31,415 None 3 31415 -1(nearest 10) =ROUND(31415,-1) 31,420 Up 4 31415 -2(nearest 100) =ROUND(31415,-2) 31,400 Down 5 31415 -3(nearest 1,000) =ROUND(31415,-3) 31,000 Down 6 31415 -4(nearest 10,000) =ROUND(31415,-4) 30,000 Down 7 31415 -5(nearest 100,000) =ROUND(31415,-5) 0 Down

An easy way to remember what you're rounding to when using negative [places] is to think about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

• [places] : -1 = 101 = 10
• [places] : -2 = 102 = 100
• [places] : -3 = 103 = 1000
• [places] : -4 = 104 = 10000
• [places] : -5 = 105 = 100000

#### MROUND Function

=MROUND(value,factor)

The MROUND function takes a number (value) and rounds it up or down to the nearest multiple of the specified factor following specific rules:

• Both value and factor must both be positive or both be negative.
• If either value or factor is zero, MROUND will return 0.
• If value is equally close to two multiples of factor, the greater multiple (using absolute value) will be returned. (This is the same as the ROUND function: Negative numbers are rounded down towards 0 and rounded up away from 0.)

The value and factor do not have to be whole numbers. You can round a decimal to a multiple of another decimal.

Here's a few examples to show you how it works:

 A B C D E 1 value factor Formula Output Rounding 2 17.86 1 =MROUND(17.86,1) 18 Up 3 17.86 3 =MROUND(17.86,3) 18 Up 4 -17.86 -5 =MROUND(-17.86,-5) -20 Up 5 -17.86 -15 =MROUND(-17.86,-15) -15 Down 6 17.86 2.5 =MROUND(17.86,2.5) 17.5 Down 7 17.86 7 =MROUND(17.86,7) 21 Up

### Round Down With Google Sheets Functions

For rounding down you can choose one of the following:

• ROUNDDOWN is for rounding down to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• FLOOR is for rounding down to the nearest multiple of a specified factor (e.g. nearest 5, 10, 1000, 0.5).
• INT is for rounding down to the nearest whole number.

#### ROUNDDOWN Function

=ROUNDDOWN(value,[places])

The ROUNDDOWN function takes a number (value) and rounds it down to the specified number of decimal places.

[places] is an optional argument that is assumed to be 0 if left out.

Negative numbers are rounded towards 0.

The ROUNDDOWN function works just like the ROUND function except that it always rounds down.

Here's a few examples to show you how it works:

 A B C D E 1 value [places] Formula Output Rounding 2 3.1415 0 =ROUNDDOWN(3.1415) 3 Down 3 3.1415 1 =ROUNDDOWN(3.1415,1) 3.1 Down 4 3.1415 2 =ROUNDDOWN(3.1415,2) 3.14 Down 5 3.1415 3 =ROUNDDOWN(3.1415,3) 3.141 Down 6 -3.1415 3 =ROUNDDOWN(-3.1415,3) -3.141 Down (towards 0) 7 3.1415 4 =ROUNDDOWN(3.1415,4) 3.1415 None 8 3.1415 5 =ROUNDDOWN(3.1415,5) 3.1415 None

As with the ROUND function, when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

If [places] is negative, ROUNDDOWN will round down to a specific number of places to the left of the decimal point:

 A B C D E 1 value [places] Formula Output Rounding 2 31415 0 =ROUNDDOWN(31415) 31,415 None 3 31415 -1(nearest 10) =ROUNDDOWN(31415,-1) 31,410 Down 4 31415 -2(nearest 100) =ROUNDDOWN(31415,-2) 31,400 Down 5 31415 -3(nearest 1,000) =ROUNDDOWN(31415,-3) 31,000 Down 6 31415 -4(nearest 10,000) =ROUNDDOWN(31415,-4) 30,000 Down 7 31415 -5(nearest 100,000) =ROUNDDOWN(31415,-5) 0 Down

Remember what you're rounding to when using negative [places] by thinking about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

• [places] : -1 = 101 = 10
• [places] : -2 = 102 = 100
• [places] : -3 = 103 = 1000
• [places] : -4 = 104 = 10000
• [places] : -5 = 105 = 100000

#### FLOOR Function

=FLOOR(value,[factor])

The FLOOR function takes a number (value) and rounds it down to the nearest multiple of the factor following specific rules:

• value can be positive or negative.
• When value is positive, [factor] must be positive (and not 0).
• When value is negative, [factor] can be:
• Positive or -1: value is rounded away from 0.
• Negative (<= -2): value is rounded toward 0.
• Not equal to 0.

[factor] is an optional argument that is assumed to be 1 if left out (so the function rounds down to the nearest whole number and away from 0 if negative).

FLOOR is often used in financial formulas to get currency values to the nearest \$0.05 or \$0.25.

Here's a few examples to show you how it works:

 A B C D E 1 value [factor] Formula Output Rounding 2 17.86 1 =FLOOR(17.86,1) 17 Down 3 17.86 3 =FLOOR(17.86,3) 15 Down 4 -17.86 -5 =FLOOR(-17.86,-5) -15 Toward 0 5 -17.86 -15 =FLOOR(-17.86,-15) -15 Toward 0 6 -17.86 -15 =FLOOR(-17.86,15) -30 Away from 0 7 17.86 7 =FLOOR(17.86,7) 14 Down

FLOOR has two sister functions (FLOOR.PRECISE and FLOOR.MATH). These functions have similar arguments but can provide more flexibility, particularly when dealing with negative numbers and varying the way they round (towards or away from 0).

#### INT Function

=INT(value)

The INT function takes a number (value) and rounds it down to the nearest whole number (also known as an INTeger).

Negative numbers are rounded down away from 0.

For positive numbers, another way to describe this function is that INT simply cuts off the decimals: 3.141 cuts to 3.141 = 3.

Because INT rounds down away from 0, it is effectively the same as:

• FLOOR with a negative value and factor of ±1.
• CEILING with a negative value and factor of -1.

Here's a few examples to show you how INT works:

 A B C D 1 value Formula Output Rounding 2 9.4 =INT(9.4) 9 Down 3 -16.2 =INT(-16.2) -17 Away from 0 4 21 =INT(21) 21 None 5 -18.2 =INT(-18.2) -19 Away from 0 6 6.1 =INT(6.1) 6 Down 7 -2.5 =INT(-2.5) -3 Away from 0

### Round Up With Google Sheets Functions

For rounding up you can choose between:

• ROUNDUP is for rounding up to a specific number of decimal places or to the nearest power of 10 (e.g. nearest 10, 100, 1000).
• CEILING is for rounding up to the nearest multiple of a specified factor (e.g. nearest 5, 10, 1000, 0.5).
• ODD is for rounding down to the nearest whole, odd number.
• EVEN is for rounding down to the nearest whole, even number.

#### ROUNDUP Function

=ROUNDUP(value,[places])

The ROUNDUP function takes a number (value) and rounds it up to the specified number of decimal places.

[places] is an optional argument that is assumed to be 0 if left out.

Negative numbers are rounded away from 0.

The ROUNDUP function works just like the ROUND function except that it always rounds up.

Here's a few examples to show you how it works:

 A B C D E 1 value [places] Formula Output Rounding 2 3.1415 0 =ROUNDUP(3.1415) 4 Up 3 3.1415 1 =ROUNDUP(3.1415,1) 3.2 Up 4 3.1415 2 =ROUNDUP(3.1415,2) 3.15 Up 5 3.1415 3 =ROUNDUP(3.1415,3) 3.142 Up 6 -3.1415 3 =ROUNDUP(-3.1415,3) -3.142 Up 7 3.1415 4 =ROUNDUP(3.1415,4) 3.1415 None 8 3.1415 5 =ROUNDUP(3.1415,5) 3.1415 None

As with the ROUND function, when [places] is greater than the number of decimal places present, zeros aren't appended to the number (e.g. 3.14150). Instead, insignificant zeros aren't displayed. (Formatting applied to a cell can change this.)

If [places] is negative, ROUNDUP will round down to a specific number of places to the left of the decimal point:

 A B C D E 1 value [places] Formula Output Rounding 2 31415 0 =ROUNDUP(31415) 31,415 None 3 31415 -1(nearest 10) =ROUNDUP(31415,-1) 31,420 Up 4 31415 -2(nearest 100) =ROUNDUP(31415,-2) 31,500 Up 5 31415 -3(nearest 1,000) =ROUNDUP(31415,-3) 32,000 Up 6 31415 -4(nearest 10,000) =ROUNDUP(31415,-4) 40,000 Up 7 31415 -5(nearest 100,000) =ROUNDUP(31415,-5) 10,0000 Up

Remember what you're rounding to when using negative [places] by thinking about the negative number as the number of zeros after 1. This is the same as saying it's rounding to the nearest 10|[places]|:

• [places] : -1 = 101 = 10
• [places] : -2 = 102 = 100
• [places] : -3 = 103 = 1000
• [places] : -4 = 104 = 10000
• [places] : -5 = 105 = 100000

#### CEILING Function

=CEILING(value,[factor])

The CEILING function takes a number (value) and rounds it up to the nearest multiple of the factor following specific rules:

• value can be positive or negative.
• When value is positive, [factor] must be positive (and not 0).
• When value is negative, [factor] can be:
• Positive: value is rounded toward 0.
• Negative: value is rounded away from 0.
• Not equal to 0.

[factor] is an optional argument that is assumed to be 1 if left out (so the function rounds up to the nearest whole number and away from 0 if negative).

CEILING is often used in financial formulas to get currency values to the nearest \$0.05 or \$0.25.

Here's a few examples to show you how it works:

 A B C D E 1 value [factor] Formula Output Rounding 2 17.86 1 =CEILING(17.86,1) 18 Down 3 17.86 3 =CEILING(17.86,3) 18 Down 4 -17.86 -5 =CEILING(-17.86,-5) -20 Away from 0 5 -17.86 -15 =CEILING(-17.86,-15) -30 Away from 0 6 -17.86 -15 =CEILING(-17.86,15) -15 Toward 0 7 17.86 7 =CEILING(17.86,7) 21 Down

CEILING has two sister functions (CEILING.PRECISE/ISO.CEILING and CEILING.MATH). These functions have similar arguments but ultimately provide the same functionality.

#### ODD Function

=ODD(value)

The ODD function takes a number (value) and rounds it up to the nearest whole, odd number.

Negative numbers are rounded up away from 0.

Here's a few examples to show you how ODD works:

 A B C D 1 value Formula Output Rounding 2 9.4 =ODD(9.4) 11 Up 3 -16.2 =ODD(-16.2) -17 Away from 0 4 21 =ODD(21) 21 None 5 -18.2 =ODD(-18.2) -19 Away from 0 6 6.1 =ODD(6.1) 7 Up 7 -2.5 =ODD(-2.5) -3 Away from 0

#### EVEN Function

=EVEN(value)

The EVEN function takes a number (value) and rounds it up to the nearest whole, even number.

Negative numbers are rounded up away from 0.

Here's a few examples to show you how EVEN works:

 A B C D 1 value Formula Output Rounding 2 9.4 =EVEN(9.4) 10 Up 3 -16.2 =EVEN(-16.2) -18 Away from 0 4 21 =EVEN(21) 22 Up 5 -18.2 =EVEN(-18.2) -20 Away from 0 6 6.1 =EVEN(6.1) 8 Up 7 -2.5 =EVEN(-2.5) -4 Away from 0

Because all even numbers are a multiple of 2, INT is effectively the same as:

• FLOOR with a negative value and factor of 2.
• CEILING with:
• a positive value and factor of 2.
• a negative value and factor of -2.

### The Problem With Rounding Using Formulas

You can run into problems if you round numbers at each stage of a multi-step calculation.

Rounding too aggressively can mean your end result isn't as correct as you may want it to be.

Let's look at an example of purchasing some items from overseas.

There's the quantity and price, but you also have to think about the exchange rate (which can go to many decimal places depending on the currency).

If you round to 2 decimal places for the dollar value of each item before totaling, you might get a different answer than if you simply round at the very last step.

Here's some numbers that show this happening:

 A B C D E 1 Quantity Price XR Totals Rounded 2 Formulas: =A3*B3*C3 =ROUND(D3,2) 3 4 \$10.49 1.278986 \$53.666253 \$53.67 4 9 \$6.28 1.278986 \$72.288289 \$72.29 5 17 \$4.23 1.278986 \$91.971883 \$91.97 6 18 \$1.65 1.278986 \$37.985884 \$37.99 7 4 \$5.95 1.278986 \$30.439867 \$30.44 8 \$286.35 \$286.36

When the prices are rounded at each step in Column E, the total is \$286.36.

When the prices are rounded at the end in Column D, the total is \$286.35.

That's only a \$0.01 difference.

But what if there were more items, higher prices, and more transactions?

These rounding errors can add up quickly.

The lesson: Be careful when rounding with functions and if you must, do it at the last step.

FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today:

## Rounding Using Formatting In Google Sheets

You can 'round' numbers up and down in Google Sheets by changing the formatting of the cell in which the number lives.

With this rounding method, you don't actually change the number (like functions do).

Instead, the number appears as rounded to the user but the full number is still available for calculations.

There are two ways to round using formatting in Google Sheets:

Both methods prevent the errors with rounding in multi-step calculations. However, it does present problems of its own.

### Rounding Using Increase / Decrease Decimal Places Buttons

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

STEP 1: Select the cell, range, column, and/or row you want to round:

Don't worry if there's text data in your selection, changing the decimal formatting won't affect it.

You can make multiple separate selections by holding down Ctrl (Windows, Chrome OS) or ⌘ (Mac) while you click.

STEP 2: Click on the 'Decrease/Increase decimal places' buttons in the toolbar:

TRY IT: You can practice using the 'Decrease decimal places' and 'Increase decimal places' toolbar buttons right now:

 A B 1 Original Formatted 2 0.57721 0.57721 3 1.618 1.618 4 2.71828 2.71828 5 3.141592654 3.141592654 6 4.6692 4.6692 7 7 7

When changing the decimal formatting of a range of cells up or down, the number of places is determined by the top-left cell.

If the top-left cell is currently rounded to 5 places, decreasing the decimal places will change the entire range to be rounded to 4 decimal places.

Similarly, increasing the decimal places will change the entire range to be rounded to 6 decimal places.

### Rounding Using Increase / Decrease Decimal Places Buttons On The Google Sheets iPhone and iPad App

STEP 1: Select the cell, range, column, or row you want to round by touching them.

STEP 2: Touch the formatting icon above the sheet:

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll to the 'Decimal places' section, and use the up and down arrows to increase or decrease the decimal places:

Here's a clip of the iPhone/iPad decimal place rounding process:

### Rounding Using Increase / Decrease Decimal Places Buttons On The Google Sheets Android App

STEP 1: Select the cell, range, column, or row you want to round by touching them.

When using the Android app be careful not to select a range that includes text data. Doing so means the decimal place increase and decrease buttons won't work.

I suspect this is a bug in the software that will be fixed in the future as on all other platforms text data is simply ignored.

STEP 2: Touch the formatting icon above the sheet:

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll to the 'Decimal places' section, and use the up and down arrows to increase or decrease the decimal places:

Here's a clip of the Android decimal place rounding process:

### Rounding Using Custom Number Formats

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

Custom number formats are a very powerful and versatile tool in Google Sheets.

Just one of the many things they can accomplish is rounding numbers.

STEP 1: Select the cell, range, column, and/or row you want to round:

Don't worry if there's text data in your selection, changing the custom number format won't affect it.

You can make multiple separate selections by holding down Ctrl (Windows, Chrome OS) or ⌘ (Mac) while you click.

STEP 2: Access the custom number formatting popup using either the toolbar (123 dropdown ➜ More FormatsCustom number format):

Or the main menu (FormatNumberMore FormatsCustom number format):

STEP 3: You'll now see the custom number formatting popup. Here you can change the input text to change the number formatting:

Notice that the 'Sample' provides you with a live update of how your entry will affect a sample number.

Here are some examples of custom number formats and their output:

 A B C D 1 Original Formatting Description Output 2 0.57721 #,##0 Whole number 1 3 1.618 #,##0.0 1 decimal place 1.6 4 2.71828 #,##0.00 2 decimal places 2.72 5 3.141592265 #,##0.000 3 decimal places 3.142 6 4.6692 #,##0.0000 4 decimal places 4.6692 7 7 #,##0.00000 5 decimal places 7.00000

The hashes (#) hide insignificant zeros and the comma (,) helps readability for large numbers. You can change this if it's not suitable for your specific situation.

There is a quick way to format to two decimal places.

You can simply choose the standard Number formatting:

You achieve the same thing using the following keyboard shortcuts:

• Windows or Chrome OS:
• Ctrl+Shift+1
• Alt+H,K
• Alt+O,N,N
• Mac:
• Ctrl+Shift+1

### Rounding Using The Number Format On The Google Sheets iPhone and iPad App

Unfortunately, specifically designed custom number format inputting (e.g. #,##0.0000) is not accessible on the iPhone and iPad app.

However, you can still access the standard Number format (e.g. #,##0.00) to quickly round to two decimal places.

STEP 1: Select the cell, range, column, and/or row you want to round.

STEP 2: Touch the formatting icon above the sheet:

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll down and touch the 'Number format' section:

STEP 4: In the new menu, select 'Number':

### Rounding Using The Number Format On The Google Sheets Android App

Unfortunately, specifically designed custom number format inputting (e.g. #,##0.0000) is not accessible on the Android app.

However, you can still access the standard Number format (e.g. #,##0.00) to quickly round to two decimal places.

STEP 1: Select the cell, range, column, and/or row you want to round.

STEP 2: Touch the formatting icon above the sheet:

STEP 3: Navigate to the 'Cell' tab of the formatting menu, scroll down and touch the 'Number format' section:

STEP 4: In the new menu, select 'Number':

### The Problem With Rounding Using Formatting

Rounding using formatting gets you the most correct answer because the numbers within calculations aren't rounded throughout the process.

However, this can lead to results that don't look correct.

Take a look at these numbers:

 A B C 1 1 1.55 1.6 2 2 3.26 3.3 3 Total 4.81 4.8

Column B works and makes sense.

Column C contains the numbers from Column B rounded to 1 decimal place.

Here we see the problem:

1.6 + 3.3 ≠ 4.8

The calculation is 100% correct. But it looks like the spreadsheet is getting the wrong answer.

Be mindful of the potential for this issue when rounding using formatting in Google Sheets.

FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today: