Join Dennis Taylor for an in-depth discussion in this video Working with ROUND, ROUNDUP, and ROUNDDOWN, part of Excel 2013: Advanced Formulas and Functions.
Excel has any number of different rounding functions. And it's really important to use them in situations where you're coming up with answers where it's important o have your answers accurate to the penny. In this worksheet, we've got a formula in cell E2 and it's showing an increase on the price that's in cell D2. And eventually, all the entries in column E that we're going to put in, are going to be based on a 2.34% increase. That's in cell G1, so the formula's all set up, and it's given us an answer of $9.
A simple test of this might reveal something a little different. Suppose somebody orders 10,000 of these items, or is about to. We're going to write a formula here. Equal 10,000 times this 9. And of course, our answer's going to be 90,000. Except that it isn't, back to our $43.14 short here. Is that 10,000? Well, you can look in the formula bar, it surely is. Is this $9.00? Can't really tell by looking in the formula bar. But, on the Home tab, in the number group of the ribbon, let's click the button for Increase Decimal. Now, as I do this, watch cell E2 and also keep and eye on F2. Cell F2 is not changing.
So, when we increase the decimals here, all we're doing is showing more decimals. We're not changing the value at all. And whether we increase or decrease, we are not changing the value and it's a common mistake with Excel novices. To somehow assume, that using these buttons changes values. It doesn't, not at all. So we've got a bit of a problem on our hands. Now, in a different context with real simple numbers, the problem jumps out immediately. We've got data over in column A with a total below it, right here.
And right now it makes perfect sense, 4.6, five different times adds up to 23. What if we displayed these with no decimals? I'm going to use the button here for decreasing decimals. It's pretty obvious we wouldn't accept this. Those are five 5s, aren't they? Why does it add up to 23? Well, they aren't really 5s, and we can click here, and look in the formula bar, these are 4.6s. Changing the display of the decimals does not change the content. Those are 4.6s no matter what. So, the issue here, then, is that we need to change this calculation so that we truly round it to the nearest penny. There are other ways to round, as well, too.
We'll use the Round function, left parenthesis, round is based on decimal places, comma, 2, means round this to the nearest two decimal places to the right of the decimal. Therefore meaning pennies. Now, as I click Enter here, watch the formula in cell F2 react immediately to our $9 price, and there it is. Now we've truly changed this to be $9. And we're not going to keep our display looking this way. We'll simply decrease the display of the decimals, and that's the way to say it too.
Now, similarly we've got data over in column K for Salaries. We want to calculate new salary in the same kind of way that we did these price increases. We'll give you a couple of other perspectives of how you might do this. This time we'll start with out basic formula of the existing salary, times this percent. And because we're going to be copying it, we use the function key F4, make that an absolute address, and then plus the K2, existing salary, and there's an answer. Now, with yearly salaries, we could certainly keep the pennies, but is that accurate as we see it? Once again, we might increase the decimals.
It's not, and we see, finally, what's happening here. Its not uncommon to see yearly salaries expressed as whole numbers. So what I'm going to do here is use Round, this time slightly differently than before. Let's round these to the nearest whole dollar and that's not two decimal places its zero decimal places. So we'll be rounding these to the nearest dollar. And regardless of how many displays here we have decimalized, we have an answer here, and we'll just Drag this down a few cells just to check it out.
So all these are now to the nearest whole dollar, and we might leave it that way. However, it's also not uncommon to see yearly salaries as multiples of 100. I've seen that from time to time. I don't know how widespread it is, but, what if we want to change our calculation and make sure that we round our new salaries to the nearest $100. Instead of zero here, we use minus two. And I'll press Ctrl+Enter for just the first one here. And remember that was 36,444. And if we drag this out into the next cell.
It's going to go down to 68,000. Next one down, 78,300 and so on. Where's the next one going to go? We don't see the raw number underneath so we don't necessarily know it went up here. Next one's going to go up to 500 and so on. We have the possibility of saying we want this to go up to the next hundred, no matter what. And so a variation on Round is a function called Round Up. So as I press F2 here, to Edit, I'll use Round Up. Now, this is going to take us upward to the next 100, even if we're $99.99 away. So as we make a change here, remember this had been 36,444. We had taken it down to 36,400, now, by using round up, we're going to the next 100 upward.
So, sometimes you want to use that function, and round down, of course, the opposite of it, all we need to think of here is what we do with our ages. If you are 39 years and 11 months old, well you're 39 but we can certainly use that here as well, too. So 3 different functions, round being the most important ways to make sure that the calculations that we create truly are accurate. And many times when we use these functions we want to emphasise the display of the decimals and eventually decrease to get these in a more sensible display.
So no question. Round, Round Up and Round Down, valuable functions for controlling our data.
- Displaying and highlighting formulas
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Checking for errors with formulas