Working with ROUND, ROUNDUP, and ROUNDDOWN

- Excel has a number of different rounding functions and they're important because you want to be careful not to use what appeared to be rounding tools for example on the Home tab. Here's a formula in cell E2 on this worksheet called basic rounding. We've got some items listed in column C by the item number. We've got current prices in column D and we've decided to apply a 2.34% increase to all these items. Here's a formula in cell E2, doing what it's supposed to do.

We're simply increasing this by the resent we see in G1 and then adding it to the original price so we have a new price of $9. Now, what if somebody orders 10,000 of these? Theoretical situation equal this amount times 10,000. You can probably do the math in your head. It's going to be 90,000 yet the answer certainly doesn't look like that. When you look at it, you say well wait a minute here. That's what? $43.14 off. What's happening here? Is this really nine? Now when you write formulas, usually you're not increasing the number of decimals but you can from the Home tab in the ribbon.

Here's a button right here for decreasing decimals. Here's one for increasing. The important thing about these buttons is that they are formatting buttons. They have nothing to do with the real content. Now this formula here is based on what's in this cell in the 10,000. If we change the display of this but not the content, the formula to the right will not change at all. Let's change the look of this by increasing the number of decimals. Remember we're not changing its content, increase.

Here's the real value or is it? Let's keep increasing. Now when we write formulas very often in situations like this, we don't necessarily think out and perhaps we should the real implications of a formula. Here's the actual result. Showing fewer decimals does not change the result. This number over here is still looking the same. As we decrease or increase the number of decimals, we can go down to there if we wish. We're still getting the same answer. What should we do? We don't necessarily have to display this although it's not a bad idea.

We really should round the result here. As I double click in this cell, the basic rounding function is called round. Left parenthesis followed by either a number or a cell reference or in this case a calculation and then a comma. Now we put in the number of decimal places that we wish to round this to. In this case two decimals are equivalent to pennies would make sense. So put in the two. Now as I press enter, we will still see $9 if we only had a display with two decimals but since we have our display with multiple decimals, it will be nine points and a few zeros to the right.

Furthermore the enter in the cell F2 that calculation will truly become 90,000 and there it is. It wasn't truly necessary to show more decimals here but it certainly brought out the idea that the result here is now different than it was before. That's really necessary to do at times in these kinds of situations. Now if we always want to make sure that this goes up to the next penny, let me first drag these down or just double click to copy all these.

Instead of round, we could be using round up. That means go up, in other words avoid or don't use standard rounding techniques but go up to the next two decimal level no matter what. If we do this but first and probably we'll not change but it could go to 9.01. I'll change that, it didn't. The next one down could go up to 3.28. I'll try that one. It did, next one might go up, it might not but you see what's happening here.

This is always gonna go up to the next penny. Not the nearest penny but the next penny upward if we use roundup. Sure enough there's a round down. That means go down to the next nearest two decimal entry. We're gonna go down to 899 and some of these others will drop too. Next one down to 327, next one is or 25 and so on. If you have any problems remembering these, think of round down as being what we do with our ages. If you're 39 years and 11 months old, how old are you? Well, you're 39, you haven't reached 40 so you're still 39.

That's what round down does and there's some other functions that come into play here too. These three are listed here off to the right. Keep in mind if we are working with different kinds of data like salary data that we see here, a similar formula here would work equal this salary times the percent that we're going to increase all salaries by. If we want this to be absolute, we'll press command T and then plus the salary. When we're looking at a display like this, we might say okay, that's seven cents but is it really seven cents? Let's increase the decimals at least momentarily.

That's the real answer and so here too we might want to do a round. On the yearly salary it's not uncommon to see yearly salaries be whole numbers. Possibly we'll put in round right here. If we wanna round this to the nearest penny like before we put in comma two but if we wish to round it to the nearest whole dollar, that's a zero decimal place as we put in the zero. The seven cents gets lost in this example here. Now I'm gonna drag this down a few. We wouldn't normally keep the displayed like that for long but let's make another potential change here and that is round up.

The difference is more prominent than it was in our previous example. Now that's 36445. I press command Z, it was 36444. Round down. Now we're only changing within a dollar here but we'll see different choices here. I'm using round down right now so that might go to next dollar down, it didn't, that one does, drops down. That one stays the same, next one ends in 465, stays the same. That dropped down to 133 and so on. I've also seen salaries in some cases.

In one organization I worked with, they always made adjustments to the salary so there were multiples of a 100. I'm not sure exactly how they did it. They could have done it this way but if we round to the nearest 100, that means there's going to be a change in some of these calculations as much as $49.99 and we can do that by using minus two. This represents two decimal places to the left so as I drag this one down, you can see the next one is going to go to 68,000 exactly. The next one we'll go to 78,300 and the next entry to 43,000.

We don't know necessarily whether it's going to go up or down. I don't remember the previous entry but I'm gonna make a change. It went up and here too like doing a round up that means it's going to go to the next 100 no matter what. Conceivably some of these could go up as much as $99.99 if we did a round up. Based on our previous example of round down, you can see how it might change these as well too. Powerful tools here for adjusting the actual calculations. Again, never make the mistake of using the increase decimal or decrease decimal buttons to change the value.

It simply changes the display.