ROUND Function belongs to the family of Mathematical functions in Google Sheet used for rounding numbers.
While there are other functions like TRUNC/INT/CEILING/FLOOR , ROUND is the most commonly used Function and will cater for most of your needs.
Lets cover all the necessary dimension to get a complete understanding of ROUND Function.
Table of Contents
Purpose of ROUND Function
As anticipated, it helps us to round numbers and retain decimals only upto a specified decimal place. It can both round up like examples below:
ROUND(32.76,1) equals 32.8or round-down like:-
FLOOR(32.73, 1) equals 32.7We’ll come to how ROUND works in a moment. Lets understand the syntax of the function first.
Syntax and Parameter Definition
= ROUND(value, [places])- value: The number you want to round
- [places]: The number of decimal digits you want to round the ‘value’ to.
Note:
- Value can take only numerical input
- Any form of non-numerical input(like “abc”, “xyz” etc) for value will result in an “#VALUE!” error.
- [factor] is an optional input. 0 is it’s default value.
- [factor] should be an integer either positive or negative.
- You can directly add static values or cell references for both value and [places]
Expected Output, logic behind it and Examples
Round function follows a very simple logic to operate.
Firstly, it trims the number with the number of decimal places you have supplied. Then it looks at the digit at the position places + 1. If that number it <=5 then the trimmed output will be returned as it is.
If the digit at places+1 is a >5 then it returns the trimmed output with the last decimal digit increased by 1.
Below, examples will help make this more clear.
Simplest Case – ROUND Function
Below is a text book example of how ROUND Function works.
In both cases we want to round the ‘value’ to 1 decimal place. In the first case since the digit at 2nd decimal place(1) is less than 5, hence 253.2 is the output. This is ’round-down’ operation.
In the second case, the digit at the 2nd decimal place is 7, which is greater than 5, hence the final output is 253.3 instead of 253.2. The increase in the last digit from 2 to 3 is due to 7>5. This is ’round-up’ operation.
Complex Value/Places Combination with ROUND Function
If the above example was clear then lets make things a little more complex this time. In the below set of examples, we have a more complex value and different degree to which we wish to round.
All the examples highlighted in orange have been ’rounded-up’. The simple reason being that the digit at places+1 was greater than 5. In the first example, 10.13 is the output as digit in 2rd decimal place 6 is greater than 5.
The non-highlighted examples have simply been ’rounded-down’
Working with Negative Value
We used the same numbers from last example, just added a minus sign before it. As you can see. There is no impact of having a negative value. ROUND operates as it’s meant to do.
Working with Negative Factor
This is a worthy mention to before we end this piece. As already mentioned in above sections that [factor] can take negative inputs. Lets see what happens then.
As you can see below, negative factors essentally end up rounding the numbers to nearest Tens, Hundred and Thousands, depending on the factor.
A factor of -1 will round up the number to nearest 10’s , -2 to 100’s and so on.
Visual Demo of ROUND Function
Before we end here is a sample visual demonstration of ROUND Function in action.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
