Google Sheets is largely a smart tool that features smart functions which can help you in almost all of your spreadsheet procedures.
However, these features can get a little bit tricky with some formats, which can hinder progress, and require more manual and complicated work.
Rounding up or rounding down numbers works fine in Google Sheets but when it comes to rounding percentages, the functions stop being accurate.
This is due to the Google Sheets different computation of percentage numbers, but we will get into that as we move on.
In this article, we will learn all about rounding in general and then go into detail when it concerns percentages, rounding up, rounding down, and the different options available to us.
The ROUND Function
One commonly used function is the ROUND Function, a handy feature that rounds your number up or down to your preferred place.
The ROUND Function is written like this:
=ROUND(value, [places])
The ROUND Function works completely fine with regular numbers. The function itself is composed of two parameters, the value parameter, and the places parameter.
The value parameter is where we type in the number value, while the places parameter is where we type in the place we want the number to be rounded to (like tenths, hundredths, etc.)
Using this feature on percentages, however, always gets messed up in the places parameter field, where it does not correctly follow the tenths, hundredths, or other places while rounding.
This is due to the computing of percentages, where the percentage number is computed in its decimal form, making 97% become 0.97 and so on.
The proper way to solve this issue is by slightly tweaking the function formula and adding a (+2) at the end of the places parameter.
The ROUND Function for percentages is written as follows:
=ROUND(value, [places+2])
Let us break it further down:
- =ROUND is the function used in this calculation to round up numbers.
- value is the parameter where the percentage value is written.
- [places+2] is the parameter for the preferred rounding place, plus 2 for fixing the percentage places issue.
To use this function, start by filling in your spreadsheet with your percentage value or set of values.
Google Sheets automatically rounds up your percentages, so use the Decrease and Increase Decimal Places buttons in the Google Sheets menu to control how much of your percentage number is shown.
In our spreadsheet, our percentages are written in column A, column B has our preferred rounding place (which, for this step, is 2), and we will write our function in column C which will be our results column.
We will write the following formula in a blank cell in column C:
=ROUND (A2,(B2+2))
OR
=ROUND(A2,B2+2)
Then drag down from the cell to apply the function to the rest of your percentage values.
In the above example, all of our percentages are rounded up to the hundredths place.
If we wanted to change this we would simply change the Places column cells from 2 to any other preferred number. You could also write (4) instead of (B2+2) or (2+2).
Let us look at that same spreadsheet example, but with different numbers in the Places column, and analyze our results.
Using the same function formula as follows:
=ROUND(A2,B2+2)
As we can see, percentages are correctly rounded up according to the place we determined. Thus, the issue from before is successfully fixed.
The ROUNDUP and ROUNDDOWN Functions
The ROUNDUP and ROUNDDOWN Functions perform the same function as ROUND does, with an added detail.
The ROUNDUP Function is strictly used for the rounding up operations, while the ROUNDDOWN Function is strictly used for the rounding down operations.
Let us start with the ROUNDUP Function, in the same spreadsheet we created before, choose a blank cell in the Results column (column C), and type in the following formula:
=ROUNDUP(A2,B2+2)
Drag down from the cell to apply to the rest of the cells, or manually type in the formula in any preferred cell.
The results show us almost the same percentages as the ROUND Function did, this is due to both of these functions performing the same computation.
It gets a little bit different with the ROUNDDOWN Function, however, as it decreases the number in the specified place parameter. Let us explore that.
We can see this by applying the function formula to the same spreadsheet. In a blank cell in column C (the Results column), type in the following formula:
=ROUNDDOWN(A2,(B2+2))
Drag down from the cell to apply to the rest of the cells, or manually type in the formula in any preferred cell.
As the above picture shows, the numbers in the specified places have been rounded down to the nearest number below.
How to Round Percentages in Google Sheets
To round percentages in Google Sheets, use the ROUND Function which is written as =ROUND(value, [places+2]) to perform this task.
Fill the value parameter with the percentage number or cell location, and fill the [places+2] parameter with the rounding place in number form, plus 2 for the special case of the percentage format.
So, if you were to drop the (+2), you would fill the places parameter as follows:
- Ones… 2
- Tenths… 3
- Hundredths… 4
- Thousandths… 5
- Ten-thousandths… 6
- Hundred-thousandths… 7
To Conclude
In this article, we have learned how the ROUND Function works, why it does not work well with percentages in its original form, and the right way to fix this issue by adding 2 to the places parameter.
We learned how each rounding place affects the percentage numbers and applied them on a similar spreadsheet base.
We also learned about the ROUNDUP Function and the ROUNDDOWN Function, how similar they are to the original ROUND Function, and how they differ from each other.
We hope this article was helpful, informative, and easy to follow.
