How to Round Numbers in Google Sheets (Easy Formulas)

Decimals in your data are a necessary evil. While it’s good to keep as many decimal places as you can (to ensure accuracy), it may make your spreadsheet difficult and sort of unappetizing to read.

Moreover, for the sake of uniformity and symmetry, it’s always a good idea to have all your data values rounded off to the same number of decimal places.

Google Sheets provides an assortment of functions that can help you round numbers, and we are going to take a look at 4 of them in this tutorial.

Different Rounding Functions in Google Sheets

Google Sheets provides a number of different rounding functions, each of which serves a slightly different purpose. Depending on your requirement, you can choose anyone to round off your data values:

  • ROUND
  • ROUNDUP
  • ROUNDDOWN
  • MROUND

There are still others, like the CEILING and FLOOR functions, but we are just going to concentrate on the above 4 functions in this tutorial.

How does rounding of numbers work (the logic behind it)

The ROUND function takes a numeric value and rounds it to a specified number of decimal places, according to standard rules.

The standard rules of rounding are as follows:

  • If the digit to the right of the digit to be rounded is less than five, then it remains unchanged. In the other words, the number is ‘rounded up’ to the nearest digit
  • If the digit to the right of the digit to be rounded is greater than or equal to 5, then it is incremented by 1. In other words, the number is ‘rounded down’ to the nearest digit.

For example, when rounding off the number 1.263 to the second decimal place, the digit to be rounded is 6. The digit to the right of 6 is 3 (which is less than 5). So the digit to be rounded remains the same, and the final result after rounding is 1.26.

If instead, you are trying to round the number 1.267 to the second decimal place, the digit to be rounded is 6. The digit to the right of 6 is 7 (which is greater than 5). So the digit to be rounded is increased by 1 and the final result after rounding is 1.27.

How to ROUND Numbers in Google Sheets (using the ROUND function)

The syntax for the ROUND function is as follows:

ROUND(value, [places])

Here,

  • value is the number that you want to round. This may be a numeric value or reference to a cell containing a numeric value.
  • places is the number of digits or decimal places to which you want to round value. This parameter is optional. When it is not specified, its value is assumed to be 0 by default.

Let us take a look at some examples and try to round the given values of column A to the number of places specified in column B, using the ROUND function:

How to Round Numbers Using the ROUND Function

From the above image you will observe that:

  • In row 2, we want to round off the value 213.146 to 1 decimal place. According to standard rounding rules, the rounding digit, 1 remains the same. So, the rounded number now becomes 213.1. We have essentially ‘rounded down’ the value 213.145.
  • In row 3, we want to round off the value 213.146 to 2 decimal places. According to standard rounding rules, the rounding digit, 4 is increased by 1. The rounded number now becomes 213.15. We have essentially ‘rounded up the value 213.145.
  • In row 5, there is no places parameter provided in the formula, so the default value 0 will be used. This means we want to round off the value 213.146 to 0 decimal places, in other words, to the nearest integer. According to standard rounding rules, the nearest integer to 213.146 is 213. Again, we essentially ‘rounded down’ the value 213.146.
  • In row 10, there is again no places parameter. So we again need to round off the value to the nearest integer. The nearest integer to 213.642 is 214. Here, we essentially ‘rounded up’ the value 213.642.

The ROUND function can also be used with negative values for the places parameter. In such cases, the value is rounded to the left of the decimal point. So,

  • if places is -1, then the ROUND function will round the value to the nearest tens.
  • if places is -2, then the ROUND function will round the value to the nearest hundreds.
  • if places is -3, then the ROUND function will round the value to the nearest thousands.

and so on.

Let us take a look at a few more examples to understand how the ROUND function works with negative values for the place parameter:

ROUND Function Examples

From the above image you will observe that:

  • In row 2, we round off the value 213.146 to -1 places. The function removes all digits on the right of the decimal point. It then rounds the value to the left of the decimal point to the nearest tens. The nearest tens to the number 13 is 10. So, the function rounds down the value to 210.
  • In row 3, we round off the value 213.146 to -2 places. The function rounds the integer portion of the value to the nearest hundreds. The nearest hundreds to the number 213 is 200. So, the function rounds down the value to 200.
  • In row 6, we round off the value 266.142 to -1 places. The function rounds the integer portion of the value to the nearest tens. The nearest tens to the number 66 is 70. So, the function rounds up the value to 270.
  • In row 9, we round off the value 656.142 to -3 places. The function rounds the integer portion of the value to the nearest thousands. The nearest thousands to the number 656 is 1000. So, the function rounds up the value to 1000.

It’s clear from the above examples that the ROUND function either rounds up or rounds down the given value, depending on the standard rounding rules. But what if you wanted to ensure that your value only gets rounded up and never down?

For such cases, you can make use of the ROUNDUP Google Sheets function.

How to RoundUp Numbers in Google Sheets

The ROUNDUP function works the same way as the ROUND function, except that it always rounds the value upward.

Syntax of the ROUNDUP Function

The syntax for the ROUNDUP function is the same as that of the ROUND function:

ROUNDUP(value, [places])

Examples using the ROUNDUP Function

Let us take a look at some examples to see how the ROUNDUP function works:

ROUNDUP formulas in Google Sheets

From the above image, it is quite clear that the ROUNDUP function always rounds up the value to the given number of decimal places.

Similar to the ROUND function, the ROUNDUP function also supports negative values for the places parameter.

Here are some examples to help you understand how the ROUNDUP function works with negative values for the places parameters:

ROUNDUP formulas with negative values

How to RoundDown Numbers in Google Sheets

The ROUNDDOWN function works the same way as the ROUND function, except that it always rounds the value downward.

Syntax of the ROUNDDOWN Function

The syntax for the ROUNDDOWN function is the same as that of the ROUND function:

ROUNDDOWN(value, [places])

Examples using the ROUNDDOWN Function

Let us take a look at some examples to see how the ROUNDDOWN function works:

ROUNDDOWN formulas in Google Sheets

From the above image, it is quite clear that the ROUNDDOWN function always rounds down the value to the given number of decimal places.

Similar to the ROUND function, the ROUNDDOWN function also supports negative values for the places parameter.

Here are some examples to help you understand how the ROUNDDOWN function works with negative values for the places parameters:

ROUNDDOWN formulas with negative values

How to Round to the Nearest Integer Multiple (MROUND Function)

Using negative values with the above rounding functions can help convert your numbers to the nearest multiples of 10, 100, 1000, etc. However, what if you want to convert values to the nearest multiple of some other number, like 2, 3, 15, etc.?

Google Sheets has something for that too. It lets you use the MROUND function. This function works the same way as the ROUND function, except that it lets you round a value to the nearest integer multiple of another value.

Syntax of the MROUND Function

The syntax for the MROUND function is similar to that of the ROUND function:

MROUND(value, factor)

Here,

  • value is the number you want to round.
  • factor is the number to whose multiple the value will be rounded.

Unlike ROUND, ROUNDDOWN, and ROUNDUP functions, you cannot use negative values in the second parameter of the MROUND function, unless the first parameter is also a negative number.

Examples using the MROUND Function

Let us take a look at some examples to see how the MROUND function works:

MROUND Function in Google Sheets

From the above image you will observe that:

  • In row 2, the MROUND function rounds the value 213.142 to the nearest multiple of 2. So, we get the result as 214.
  • Similarly, in row 9, the MROUND function rounds the value 565.142 to the nearest multiple of 15. So, we get the result as 570.

In this tutorial, we showed you how to round numbers in Google Sheets using four different rounding functions in Google Sheets. These included the ROUND, ROUNDUP, ROUNDDOWN, and MROUND functions.

The ROUND function can help you round values according to standard rules, while the ROUNDUP function ensures that your values always get rounded upwards. Similarly, the ROUNDDOWN function ensures that your values always get rounded downwards.

The MROUND function, on the other hand, lets you round values to multiples of a certain integer.

Once you know what each rounding function does, applying them according to your needs becomes easy.

We hope this tutorial has enabled you to understand the differences between each of the rounding functions and how best to use them in Google Sheets.

Other Google Sheets tutorials you may like:

Leave a Comment