How and when to use the 10 rounding functions in Excel

- [Instructor] In Excel, there are 10 different functions that relate to rounding capability. By far, the most common is the Round Function. It's got two companions, RoundUp and RoundDown, and also an unusual function called MRound that some people might need when rounding to numbers that are not decimally oriented, and it too, has some variations for moving up and down. And the other four, which we'll see, are not so widely used. Let's first talk about, what we've got going on in cell E2. Here's a formula that's calculating a new price.

All these prices in column D are about to have a price increase, 4.36%, and here's the formula setup in cell E2, as I double click it, and it gives us an answer, $9.00. What if somebody orders 10,000 of these? Maybe this is office equipment, equal 10,000, times this amount. You know the answer ahead of time, it's 90,000, but that's not the answer we're getting. And who gets that or who doesn't get the $41.68 that's hanging out here, the difference? That's a substantial difference over time, perhaps.

That looks like $9.00, but is it? Now, keeping eye on this number, I'm gonna make a change to this, I'm going to show more decimals. Now, on the Home tab number group, there are two buttons here, one for increasing the decimal, one for decreasing. I can't tell you how often I've heard and seen people use these buttons to assume that they are changing values. These are formatting tools only, they change the appearance. If we're going to increase the decimal here, we will see a change, but because we're not really changing content, this formula is going to keep the same result.

But let's show more decimals by increasing the decimal. Now, keep doing that for a while. That's the real result of the formula, regardless of how many decimals we show, and the formula to the right, is not changing at all. So what should we have done here, or what should, whoever wrote the formula, have done? That person probably should not have assumed that the answer was a nice, even $9.00 here. Now, showing more decimals is handy at times, to remind yourself what the real answer is, but what we really should've done here, is to round this calculation.

Let me double click again to expose the formula. We need to round this calculation, probably to the nearest penny. Although in some cases you could round it to the nearest nine cents, the nearest 10 cents, the nearest whole dollar. Let's use the function called Round. The Round function takes a calculation, for example this one, and then, with a comma, we indicate how many decimal places we want to round this to. And so if we do put in the number two, we're saying round it to the nearest two decimals, or the nearest penny.

If I were to put in one right here, we would be rounding this to the nearest 10 cents. Now of course, at times, using one or two ends up with the same result, but certainly not always. So, as I press control + enter here, an active cell will now move downward, get an answer, and as I do, we will be truly rounding this to $9.00 and the formula to the right will react immediately, and we see that happening. Over time, of course, we don't really need to see these decimals, but it's certainly is something you wanna keep an eye on when you're writing certain formulas.

I'll drag this down just a few cells, we'll see what's happening here. In all these cases here, we don't necessarily know what we would've seen before, but we're rounding these in all cases. Now, sometimes you want to round up, meaning, in this case, go up to the next penny. Now, that means in fact, in some cases we will see a different result here. I'm gonna put in letters U-P right here, will it change the first one? Not necessarily. Next one is $3.34, now let's do it with the RoundUp, that stays the same, how about the next one? That ends in 34 cents as well, it went up to 35 cents.

Next one. Let's see, that one didn't change, but at times it will. In other words, override standard rounding techniques, go up to the next level. If we put in down, as you might guess, we'll have some changes here. RoundDown to the next, in this case two decimals, in other words, move down to the next penny even if you're far away from it, so to speak. So that it take it downward, and the next one, that goes down, next one, and the next one.

And we see what's happening. A good way to remember what this does, think of what you do with your age. If you are 39 years and 11 months old, how old are you? Well, you're 39. So we round it down when we talk about our ages, unless you're six or seven years old, maybe, something like that. So that's easily used and we can see what's happening in here, but anytime you're writing formulas, do think out that idea. What if all along we had this column displayed, as many people do, they'll click comma, two decimal display, that's what we use for prices, is this the exact answer? Well it is since we used the function, but we saw what happened earlier, keep an eye on this, and just to be safe, when you're writing formulas like this, either on the whole column or on selected cells, increase the decimal just to see what we've got here.

And there certainly will be some times when that goes to the third, fourth, or fifth digit. Now, off to the right here, I've got some other data, salary data, obviously higher in magnitude, and I've got a formula already set up, very similar to the other formula we say, and this is a 2.43% increase, and we see the answer there. Is that the pure number? Let's increase the decimal. Not quite, now here it has a very small meaning, because that's a yearly salary and what do we see there? An extra 12 one hundredths of a cent, who would care? But on the other hand, we've got lots of salaries and we're adding up the numbers, and we want a total that all makes sense, why not clip these off, either at the penny level, that's two decimals, or maybe at the whole dollar level.

Let me copy some of these down. They all have an absolute reference to that 2.43 amount, there's an absolute reference right there. So, here are the real results. Let's change this, first one, by using Round, and we'll round this to the nearest two decimals at first. See what's happened there? I'll just drag down a couple of these. Next is going to end in 54 cents, next one would be 76 cents, see what's happening there. And probably not surprises, based on what we'd seen earlier.

But, what if we go to zero? Zero is equivalent to a whole dollar. Make a change there, and the entry below this, 68,098, will change to 68,099, and the one below that, the 78,561, will go to 78,562, 'cause we're rounding this time to the nearest dollar. Now, I was working with a company once, and although I don't know how they came up with their calculation, I recognized in their salary schedule, that all the salaries ended in two zeros. Now, possibly when they were upgrading, they decided that would be easier to work with for calculations.

If we wanted these results here to be rounded to the nearest hundred dollars, we put in minus two. That's two characters to the left of the decimal. See what's happening. And as we copy down here, some of these will not change much at first, but that 099 will go up a dollar. See that one? The next one will go up 38 dollars, from 562 to 600, and so on. Next one will go up 27 dollars and five cents, or whatever, there goes up to 400. You can predict what's gonna happen, of course, as you work with these.

Two places to the left of the decimal. And of course, in all these examples, too, we could've done a RoundUp and a RoundDown. So in some of the examples here, if we were doing a RoundUp or a RoundDown, the change from the initial calculation and what we end up with here, could be as much as almost 100 dollars, just short of 100 dollars. Of course, the people getting the salaries probably wouldn't know that necessarily. Now, there could be times when you round to numbers that are not multiples, or not decimally oriented. I'll show you two examples.

Go back to the other data first. What if we wanted these prices to be rounded to the nearest five cents? Now there probably is a way to do that by using RoundDown and dividing it by two, some technique that way, but we can cut directly to the chase. We can round to any specific number we want by using a function called MRound. And this time instead of decimal places, be thinking of an amount, like 0.05, for example. We want to round this to the nearest five cents. I have an extra parenthesis over there, and enter.

Now I'll just copy this down, so that 3.33, probably gonna go up to 3.35, there it is, and the next one will go up to 4.35, and this one looks like it'll probably stay the same. But if we drag these down a few more, all these are to the nearest five cents, five cents. If we want to go up or down, and you've already seen the name of the function, but based on what we have seen earlier, you probably guess it will be MRoundUp or MRoundDown, but no, it's Ceiling and Floor. Ceiling for rounding up.

So Ceiling will take us up to the next nickel. Floor would take us down to the next. I'll do all those at once and some of them went up and some of them didn't. So, is that the price. Now, another way I've seen this used, too, one comes down working with pay people every two weeks, and the gross pay every two weeks was an even number. So how would they perform the calculation? They could've used MRound, and then upgraded the salaries, but made sure they were multiples of 26, there are 26 pay periods a year.

So if I complete this entry here, that's a number that's evenly divisible by 26. Now we don't need to see the decimals here, we're rounding to the nearest multiple of 26. So as I drag these downward, so these are all even multiples of 26, so every pay period the gross pay, which is one 26 of this, will be an even number. That's an unusual use of it, but you could imagine doing that. And here, too, if we always wanted to give this a slight bump upward or downward, we'd use Ceiling or Floor, instead, with the same kind of construction.

Now, on a different worksheet over here, Trunc, INT, ODD, and EVEN. Let me first show these in the left hand side of the screen. Trunc, think of the word truncate. INT, think of the word integer, and of course ODD and EVEN, you know what they are. So in the next worksheet over, I've got some numbers in column B. Now these aren't formulas, but they might well have been, but let me zoom in on these, holding down control, using the mouse wheel. So formulas in column C, we see it right there. The Trunc function and INT function are going to be the same if the entries are positive.

So here we see the Trunc version of this, what is that doing? It always drops the decimal portion. And you could say of INT, think of integer, same idea, as long as we're dealing with positive data. But, if the data is negative, we get some different results, and this surprises people at times. Trunc says what? Simply drop the decimal portion of this, just give me the whole number portion of it. Throw this away. But what happens with INT? Move away from zero, it's how it's sometimes described.

So, if we're using the integer function with numbers like that, always return a lower value, and of course minus 16 is a lower value than minus 15.58, so these are always a lower value. And again, it's sometimes described as drop the pennies, or drop the decimals, but make sure you move away from zero, not toward it. And so if you had a number like 15.58, and you were thinking that INT just throws away the decimal, well in a certain sense it does, but it wouldn't keep the 15, because that's moving closer to zero, that's why INT, when looking at the data there, comes up with 16.

Now, you're less likely to use ODD and EVEN, but nevertheless, that's a possibility, too. We've got some numbers over here, let's find the nearest odd number. Most of the time you're working with positive data, but nevertheless there still could be times when you say, I want the nearest odd number, but we're always moving away from zero, and we see how all these are playing out. So in all these examples here we're using the ODD function in column H, viewing the numbers off to the left, we're moving away from zero to the next odd number.

And of course a lot of these you can do in your head, but of course, we've got reasons to do these. So, as we see whole numbers here, 22, that is an even number, so we're not, in a sense, moving away at all, we're just rounding, but we're rounding it to the whole number 22. If that were 22.1, our answer would be 24. So, like here we see 16, 18. So, you're less likely to use these, but occasionally are gonna come in handy, much more important I think for most people, are the rounding functions Round, occasionally RoundUp, RoundDown, and then from time to time, MRound to round to numbers that are not decimally oriented.