You often come across values that you need to round off in a data set. It could be rounding up a number to the nearest two decimal points. You might need to round down a number to the nearest whole number or integer. Google Sheets provides four functions for this. Let’s cover them today.
4 ways to round numbers in Google Sheets
The four main functions to round numbers are ROUND, ROUNDUP, ROUNDDOWN and MROUND.
Rounding up on Google Sheets follows the general rules as explained below:
- If the number to the right of the digit to be rounded is less than five, then the digit remains the same.
- If the number to the right of the digit to be rounded is more than or equal to five, then the function increments the number by 1.
Method 1: Using ROUND function
1.1 Rounding off to the nearest whole number
1.2 Rounding off number to set decimal points
1.3 Rounding off numbers beyond the decimal point
Method 2: Using ROUNDUP function
2.1 Rounding up numbers to the next whole number
2.2 Rounding up numbers while keeping set decimal points intact
2.3 Rounding up numbers beyond decimal places
Method 3: Using ROUNDDOWN function
3.1 Rounding down numbers to the next whole number
3.2 Rounding down numbers while keeping set decimal points intact
3.3 Rounding down numbers beyond decimal places
Method 4: Using MROUND function
Let’s go over the functions in detail.
Method 1: Using ROUND function
1.1 Rounding off to the nearest whole number
Let’s understand the syntax of the ROUND function which we will use here.
= ROUND(Value, Factor)
Where Value is reference to the cell that needs to be rounded off. Factor is the number of places till which it needs to be rounded off.
Taking a data set as an example, follow the steps below:
Step 1: Determine the number that you need rounded off.
You need to note the value of the cell that you need to round off. Here F2 is the cell with the value that we need to round off.
Step 2: Apply the function.
Add a column adjacent to the value. Click on the column with the value; right click and insert a column to the right.
Now apply the function within this column cell. Here the syntax will be as follows, applied in column G:
= ROUND(F2)
We get 2.00 as we round 1.99. This is consistent with the rules explained before. When you apply the formula, a suggestion to autofill all cells in the column with the formula will pop up. Click on the check mark to apply it across the column if you need that.
1.2 Rounding off number to set decimal points
For this we need to use a variation of the previous syntax as follows:
= ROUND(Value, Factor)
Where Value is reference to the cell that needs to be rounded off. Factor is the decimal point till which it needs to be rounded off.
Step 1: Determine the number that you need rounded off.
Here, we are taking cell H2 as an example.
Step 2: Apply the function.
Add a column adjacent to the value. Click on the column with the value; right click and insert a column to the right.
Now apply the function within this column cell. Here the syntax will be as follows, applied in column I.
= ROUND(H2,1)
We are taking H2 which is 189.056 and we want to round it off to one decimal point.
We will get H2 rounded off to one decimal point. Here I2 is 189.10, rounded off to one decimal point.
1.3 Rounding numbers beyond the decimal point
To round off numbers beyond the decimal point, we need to specify it in negative. Tens place is -1, hundreds place is -2, and so forth.
Step 1: Determine the value to be rounded off.
Follow steps stated before to add an adjacent column. Here, it is column G.
We are taking the value of F3 which is 19.99. We need to round it off to the tens place.
Step 2: Apply the function.
We follow the steps to add an adjacent column and apply the function by writing in the following syntax.
= ROUND(F3, -1)
We see that the function rounds it up to the nearest ten which is 20 in this case.
In case this was a number like 123.455 in F3 and we wanted it rounded off to the hundreds value, we would write = ROUND(F3, -2)
The result would be 100.
Method 2: Using ROUNDUP function
2.1 Rounding up numbers to the next whole number
ROUNDUP function comes in handy when you know you need to increment the value.
The syntax of the function is as follows:
= ROUNDUP(Value)
Step 1: Determine the number that you need rounded up.
You need to note the value of the cell that you need to round up. Here H5 is the cell with the value that we need to round up.
Step 2: Apply the function.
Add a column adjacent to the value. Click on the column with the value; right click and insert a column to the right.
Now apply the function within this column cell. Here the syntax will be as follows, applied in column I:
= ROUNDUP(H5)
H5 is 539.731, being rounded off to two decimal places in the cell as a default option.
We get the result rounded up to the next whole number on applying the function, which is 540.
2.2 Rounding up numbers while keeping set decimal points intact
In order to round up a number while keeping a certain number of decimal points intact, we use the following syntax:
= ROUNDUP(Value, Factor)
Here the factor is the number of decimal places we want to preserve.
Let’s take the value in cell H5 as an example, and apply the steps as before with the syntax as follows:
= ROUNDUP(H5, 1)
We can see that the number has been rounded up to one decimal point. H5 is 539.73 and applying the function gives us 539.8.
2.3 Rounding up numbers beyond decimal places
To round up numbers beyond decimal places, we specify the factor in negative values.
-1 indicates tens place, -2 hundreds place, and so on.
Taking H5 as an example and rounding up to tens place, we get the result as follows:
Here H5 is 539.73 and is rounded to 540 on applying the ROUNDUP function.
Method 3: Using ROUNDDOWN function
ROUNDDOWN function comes in handy when you know you need to decrement the value.
The syntax of the function is as follows:
= ROUNDDOWN(Value)
3.1 Rounding down numbers to the next whole number
Step 1: Determine the number that you need rounded down.
You need to note the value of the cell that you need to round down. Here H5 is the cell with the value that we need to round down.
Step 2: Apply the function.
Add a column adjacent to the value. Click on the column with the value; right click and insert a column to the right.
Now apply the function within this column cell. Here the syntax will be as follows, applied in column I:
= ROUNDDOWN(H5)
Here H5 is 539.73 and is rounded down to 539 on applying the ROUNDDOWN function.
3.2 Rounding down numbers while keeping set decimal points intact
The syntax for rounding down numbers while keeping set number of decimal points intact is as follows:
= ROUNDDOWN(Value, Factor)
Here the factor is the number of decimal places we want to preserve.
Let’s take cell H5 as an example and apply the syntax as follows:
= ROUNDDOWN(H5, 1)
Here H5 is 539.73 and is rounded to 539.7 on applying the ROUNDDOWN function, preserving the value till one decimal point.
3.3 Rounding down numbers beyond decimal places
To round down numbers beyond decimal places, we specify the factor in negative values.
-1 indicates tens place, -2 hundreds place, and so on.
Taking H5 as an example and rounding up to tens place, we get the result as follows:
Here H5 is 539.73 and is rounded to 530 on applying the ROUNDDOWN function.
Method 4: Using MROUND function
To round off a number to the nearest multiple of another integer, we use the MROUND function.
The syntax for this function is as follows:
= MROUND (Value, Factor)
Where factor is the number to whose nearest multiple the number will be rounded off to.
Step 1: Determine the number that you need rounded off.
You need to note the value of the cell that you need to round off. Here F6 is the cell with the value that we need to round off.
Step 2: Apply the function.
Add a column adjacent to the value. Click on the column with the value; right click and insert a column to the right. Now apply the function within this column cell. Here the syntax will be as follows applied in column G.
= MROUND(F6, 2)
This is what you get after applying the function. Here the number has been rounded off to the nearest multiple of 2.
If we try this with 3 as a factor:
= MROUND(F6, 3)
We get the number rounded off to 3 now as that is the nearest multiple of the number to be rounded off to.
One can’t use negative numbers as a factor, unless the number to be rounded off is also a negative number.
Conclusion
We hope these methods prove useful when you need to round off numbers. Drop in your comments if you know other ways to do so.