14. Rounding Numbers
If you want to avoid rounding errors, the ROUND() function can help. Learn how to use ROUND(), ROUNDUP() and ROUNDDOWN() in this lesson.
- Rounding errors often occur in Excel and irritate a lot of managers
- To solve this problem, you can use the ROUND( ) function
- ROUND( ) enables you to specify the number of decimal places for a given value
- ROUNDUP( ) and ROUNDDOWN( ) are also available as functions in Excel
ALT + H , 0: Add a digit after decimal place
ALT + H , 9: Remove a digit after decimal place
F2: Jump back inside a formula
SHIFT + →: Select next cell
CTRL + D: Fill down
In our data formatting course, I showed you how to change the number of decimal places for a selected number.
Alt + H 0 to add a decimal place, and Alt + H 9 to remove a decimal place.
These commands only change the appearance of the number.
The number stored in Excel doesn't actually change.
If you want to change the value stored in Excel, you need to use the ROUND function.
ROUND accepts two values.
The first is a cell you wish to round, and the second is the number of decimal places.
Let's say I want to convert 23.92 to 23.9.
I simply write “=round” and open a bracket.
I then select a number, write a comma, and enter the number of decimal places, which in this case is 1.
I'll then close the bracket and press Enter.
And as expected, the number changes to 23.9.
If I want to round to the nearest whole number, I simply set the number of decimal places to zero.
So jumping back into my formula with F2, I'll remove the 1 decimal place and write 0.
And this now rounds 23.92 up to 24.
Excel has two other round functions, and if I write “round” in a formula, you can see that they are ROUNDDOWN and ROUNDUP.
Let's try rounding down 11.85.
And I'll round down to one decimal place.
So again, my first input will be the number itself, and next I’ll enter the number of decimal places.
And this should give me 11.8, which it does.
Now I’ll round up 22.645 to one decimal place.
So “=round up”, open a bracket, the first value will be the number itself, and then I’ll enter the number of decimal places.
And this should give me 22.7, which it does.
You might be asking yourself at this point, why would I want to use the ROUND function? And this is a good question, best explained in an example.
Let's say your manager wants you to add these three numbers together and display them as integers or whole numbers.
So I’ll first select all of the numbers, and then using Alt + H 0 and Alt +H 9 display them as whole numbers.
I'll then add these numbers together using the SUM function.
And this gives me a value of 58.
However, adding 24, 12 and 23, actually gives me 59.
This isn't a mistake, it's just a quirk in Excel based on changing the appearance of numbers, which have decimal places.
As you can imagine, this can cause some irritation and confusion among analysts and their managers who obviously expect these simple calculations to appear correctly.
To avoid this issue, we can simply use the ROUND function.
In this selected cell, I've rounded the number to zero decimal places, and I’ll simply apply this formula using the fill down command to the other cells.
If I then use the SUM function, it will add these numbers as if they are integers, and give me a value of 59.
While the actual value is 58.415, the value of 59 does show correct addition.
If you can explain to your manager why these addition errors appear to occur, I would stick with the actual numbers when at all possible.
However, if you do need to show a correct addition for whole numbers, it's good to know that the ROUND function is always available.