3 Ways To Round Numbers in Google Sheets
Spreadsheets are efficient and easy-to-use tools when you need to perform mathematical calculations using different mathematical formulas and functions.
Google Sheets is a cloud-based free to use spreadsheets app and is a reliable alternative to Microsoft Excel. Despite being a cloud-based app, Google Sheets is still a pretty powerful tool. In this article, I will talk about how can you use Google Sheets to round a number as per your requirements.
Round Functions in Google Sheets
When working with numbers you might need to round decimal numbers to a fixed number of digits. In Google Sheets, there are three functions to round numbers, you can find them below:
Let’s first see how the ROUND function works:
The ROUND (value, [places]) functions need two arguments, the first is the number you want to round or its cell reference. The second argument is another number indicating the number of decimal places you want it to round. The round function rounds the number either to its floor value or its ceiling value depending upon the original number. Let’s apply this function to a couple of numbers.
Step 1: Launch Google Sheets, start with a new blank sheet, rename it to “round” and prepare the data as shown in the reference figure.
Step 2: Click in cell D7, then click on the “Insert” menu and point your cursor to Function > Math > ROUND, and then click on the ROUND function. Or simply type in the formula bar “=ROUND (“
Step 3: Next either enter the number to be rounded directly or click on the cell containing the number (i.e. D6) to get its cell reference number and press enter.
Step 4: D7 will now contain the number 12, the floor of the original number. This is because the “=ROUND (D6)” is the same as “=ROUND (D6, 0)” i.e. zero digits after the decimal. Applying the same formula in E7 for the value in E6 gives 13 ceiling of the original number.
Step 5: In cell D8, use the ROUND function to round the decimal part to one digit after the decimal with the command “=ROUND (D6, 1)” resulting in 12.5. For the value in E6, the result is 12.6.
Step 6: In cell D9, use the ROUND function to round the decimal part to two digits after the decimal. i.e. “=ROUND (D6, 2)” resulting in no change as the number has already two numbers after the decimal. The same is the case with the contents of E6.
The ROUNDDOWN (value, [places]) is similar to the ROUND function but as the name suggests, it rounds down the number to its floor value. Follow the following steps to see it in action:
Step 1: In Google Sheets add a new sheet, rename it to round down, and prepare the data as before.
Step 2: Select cell D7 and insert the “ROUNDDOWN” function from Insert > Function > Math > ROUND or type in the formula bar “=ROUNDDOWN (“
Step 3: Repeat the same experiments as before with the ROUNDDOWN function. “=ROUNDDOWN (D6)” gives 12 in D7. For the value in E6, it gives the same result flooring the values in both cases.
Step 4: In cell D8, use the ROUNDDOWN function to round the decimal part to one digit after the decimal. i.e. “=ROUND (D6, 1)” resulting in 12.4. It rounds down the value in E6 to 12.5.
Step 5: In cell D9, tell the ROUND function to round the decimal part to two digits after the decimal. i.e. “=ROUND (D6, 2)” resulting in no change to the original value. Similarly, the value in E6 is also unchanged.
The ROUNDUP (value, [places]) is the opposite of the ROUNDDOWN function and rounds up the number indicated by the second argument to its ceiling value. Let’s repeat the same examples in the same cells to see the difference.
Step 1: In Google Sheets add a new sheet and rename it to roundup.
Step 2: Rounding up the value in D7 gives 13 with “=ROUNDUP (D6)” and Repeating for E6 also gives 13 in E7 ceiling up the values in both cases.
Step 3: In cell D8 to round up to one digit after the decimal use type “=ROUNDUP (D6, 1)” resulting in 12.5. It rounds up the value in E6 to 12.6 in E8.
Step 4: In cell D9 to round up to two digits after the decimal type “=ROUNDUP (D6, 2)” resulting in 12.45 with no change. Similarly, the value in E6 is also unchanged.
In this article I have shown how you can use the main ROUND function and its variants (RUNDOWN, ROUNDUP) to round numbers easily in Google Sheets. Repeat this exercise and experiment with negative arguments to these functions and share with us your results in the comments section.