When you are developing a formula for any calculation, and if the accuracy depends even on the decimal points of the result, then it is important to know options available for us to round off the values in Excel.
“Increase Decimals” / “Decrease Decimals” / “Format Cells”
This option is available in “Number” group under the “Home” tab. This option simply changes the appearance of the value as if it is rounded, but, the value remains the same.
In the below screenshot, the number 4.567 when rounded off to an integer, you will get 5. Now we shall see how the option ‘Decrease Decimal’ changes its appearance but not actually rounding it off:
When you double click on the cell or when you look at the formula bar, you can observe the values is not rounded off at all.
If your requirement is to display like rounded off, this option is perfectly fine. When you want the rounded value for any calculations then we need to see what other options we got.
Let’s start our journey, in order to get the actual round off values, we have few functions in Excel as mentioned below:
Firstly, we shall investigate the function ROUND.
The syntax of the formula goes like this =ROUND(number,num_digits)
In the above syntax first argument ‘number’ is the actual value which needs to be rounded off and the second argument ‘num_digits’ is the number of digits required after the decimal point.
In the below screenshot, we are using the formula to round off the value by 1 decimal point.
This formula will check whether the second decimal value is greater than or equal to 5, if it is increases the first decimal value by 1 otherwise it will be retained as it is.
The above table is self-explanatory. The requirement for the first value is to round off to one decimal point, for the second value it is two, for the third value it is three and for the last value it is only the integer without decimal values.
Let’s go to the second formula, which is ROUNDUP
ROUNDUP function adds one (+1) to the value required decimal place irrespective of whether the consequent value is greater than / less than / equal to 5. Below table is for your reference:
Look into the second value – 2.123 – Third point to decimal is less than 5, however, it adds one (+1) to the previous value – 2 and makes the number as 2.13 instead of 2.12
Let’s move on to third formula, which is ROUNDDOWN
ROUNDDOWN function retains the same value in the required decimal place irrespective of whether the consequent value is greater than / less than / equal to 5. Below table is for your reference:
In the above example, have a look at the first value 4.56, here the second decimal value is greater than 5, however, by default it retains the same value – 5 and makes the number as 4.5 instead of 4.6
Now the fourth formula, TRUNC
TRUNC function truncates (shortens) the number by removing unwanted decimal places in the value. This formula will not round off your value.
The syntax of the formula is =TRUNC(number, [num_digits])
First argument number is the actual value that needs to be truncated. num_digits is not a mandatory argument. It is the number of digits required after the decimal point.
Let’s have a look at the below screenshot to understand this formula better
Now the fifth formula, MROUND
MROUND this formula will help you to round the value to a desired multiple. Normally when we say round off a value the increment or decrement in the values happens by 1 but not in 3s or 5s or 10s. So when you want to round a value in 10s or 20s or 5s, this formula is better choice for us.
Syntax – =MROUND(number, multiple)
For instance, when I want to round off my actual values in the multiples of 6, if I feed the number 5 it will be rounded off to 6.
Note: This function gives the result as the next nearest multiple, if the actual number is greater than or equal to half of the multiple given. Else, it gives the previous nearest multiple.
CEILING always returns a number rounded to next desired multiple.
Syntax – =CEILING(number, significance)
The argument – significance is your desired multiple
In the above example, the value 7.22 is rounded off to 14 as the multiple is given as 7. Logically, the rounded off value should be 7 as the difference between actual number and the multiple (7.22-7 = 0.22) is less than 3.5 (half of the multiple). But the CEILING function always gives the next multiple as the result.
FLOOR always returns a number rounded to previous desired multiple irrespective of the difference between the actual number and the next nearest multiple, whether it is greater than/less than/equal to half of the multiple given.
In the above example, the value 4.56 is rounded off to 0 as the desired multiple is given as 5. Logically, the result must be 5 as the difference between the actual number and the multiple is greater than
Hope this posting helped you in understand different options available in excel for rounding off a value in any excel calculation.