A crucial component of data analysis and presentation is rounding numbers. Having precise and properly rounded numbers is essential when organizing data or conducting statistical analysis for financial reporting. The popular spreadsheet program Google Sheets provides a number of simple techniques for properly and efficiently rounding figures.
In this post, we will look at five quick techniques that make it easy to round numbers in Google Sheets.
How Can Google Sheets Round Numbers?
In Google Sheets, there are five different functions that can be used to round figures:
- Using the Round Function
- Using the TRUNC Function
- Using the Ceiling Function
- Using the Floor Function
- Using the Custom Formula
All these methods are explained in the blog with practical examples.
Method 1: Round Numbers Using the Round Function
To round the numbers in Google Sheets using the Round Function, we will follow the below-mentioned syntax:
=Round(number, num_digits)
The explanation of the parameters used in the above general syntax is:
- Number: This will be replaced by the number which is supposed to be rounded off
- Num_digits: This will tell that which digits should round off
To understand the above syntax, we will round the “125.9876” to two digits using the Round function:
=Round(125.9876,2)
Press the ENTER key to display the results:
Method 2: Round Numbers Using the Trunc Function
The next method to round off numbers using the Trunc function and the TRUNC function effectively truncates an integer to the provided number of decimal places by removing the decimal part. The general usage syntax of the Trunc function in Google Sheets has the same parameters as the Round function:
=TRUNC(number, num_digits)
For example, in the above example, we replace the Round with the Trunc function:
=Trunc(125.9876,2)
The result of the Trunc function is:
Method 3: Round Numbers Using the Ceiling Function
The Ceiling function is used to round off the numbers to the specified multiple in Google Sheets. The Ceiling function can be used by following the general syntax:
=CEILING(number, significance)
Where the “number” is the number that is supposed to round off and the “significance” is the multiple to which the number is supposed to round off. For example, we will use the number of the above examples and round off it to 0.1:
=Ceiling(125.9876,0.1)
The number is rounded up to the nearest multiple of 0.1:
Method 4: Round Numbers Using the Floor Function
Similar to the Ceiling function, the Floor function in Google Sheets is also used to round off the numbers to the nearest specified multiple. The parameters of the Floor function are also similar to the Ceiling function:
=Floor(number, significance)
For the understanding, we will replace the Ceiling with the Floor function in the above example:
=Floor(125.9876,0.1)
The output of the Floor function is shown:
Method 5: Round Numbers Using the Custom Formula
To create a custom formula, we can use the App script. Click on “Extensions” and then “Apps Script”:
Now type the script shown in the figure and then run the script after saving it:
Now use the “CUSTOM_ROUND()” formula to round off the number of cells B1:
=CUSTOM_ROUND(B1, 1)
The result of the CUSTOM_ROUND() is:
These are the five easiest ways to round off the numbers in Google Sheets.
Conclusion
To round numbers in Google Sheets, we can use many approaches but the quickest is by using any of the Round, Ceiling, Floor, Trunc functions or the custom formula. All these methods to round off the numbers are demonstrated with examples for the understanding of the readers.
