Sign in or start a free trial to avail of this feature.
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 Functions in Excel (00:16)
In Excel, you can adjust the number of decimal places for a selected number using Alt, H, 0 and Alt, H, 9. This changes the appearance of the number, but not the number stored in Excel. To change the stored number, you need to use one of the three rounding functions in Excel.
The ROUND function rounds a number to a specified number of decimal places. It takes two arguments. First is the number you want to round. Second is the number of decimal places to round to. Note that to round to a whole number, the number of decimal places would be zero. In addition to ROUND, you can also use the ROUNDDOWN and ROUNDUP functions. ROUNDDOWN always rounds down to the specified number of decimal places, while ROUNDUP always rounds up.
Why Use Rounding Functions (02:20)
When you add a series of numbers, Excel will add the numbers as they are stored, not as they are displayed. If the numbers are displayed with a different number of decimal places, then it’s possible that the sum Excel computes will be different from the sum of the displayed numbers.
This can cause confusion, so you can avoid this issue by using the round function to ensure that the stored number and the displayed number are the same. This will prevent any confusion from calculation giving results that appear to be wrong.
In the previous lesson, we learned how to use Excel's information functions. In this lesson, we'll learn how to change the number of decimal places in a number using rounding functions. 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 the 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 the 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.90. 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 one decimal place, and write, 0.
And this now rounds 23.92 up to 24.00. 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.80, which it does.
Now I'll round up 22.645 to one decimal place. So =ROUNDUP. 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.700, 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 correct addition for whole numbers, it's good to know that the ROUND function is always available.