Using the CEILING and FLOOR functions for specialized rounding needs

- [Instructor] Excel has a variety of different rounding functions. The most common one is the functions called ROUND, but it also has two companions, ROUNDUP and ROUNDDOWN. But there will be times when they don't do exactly what we wish they could do. All three of those functions allow us to round data on the basis of the placement of decimals either to the right or to the left. In column C here, we got some formulas here. We're calculating new prices. Simple little formula like this. Taking the data in column B, increasing it by 2.34%, that's over in D1, and doing that for all these prices. Now if you were to write a formula here to multiply this by, let's say a big number, 10,000, you're going to be surprised at the answer. 10,000 times nine is 90,000, but what's going on there? What probably should've been done is when these calculations were made for column C probably comma format was used. Let's increase the decimals here. Increase decimals. We're not changing the result here, and that formula won't change, but as we increase these we see the real result here. Ideally, what we should've done is to round these to the nearest two decimal places, and that's where you'd probably use ROUND or ROUNDDOWN or ROUNDUP. Round up to the next penny upward and so on. But what if we wanted to take these prices and round them to the nearest nickel, five cents, or even in some weird cases, perhaps, like three cents or something like that? The ROUND function doesn't give us all the capabilities to do that. There's a function called MROUND that's analogous to ROUND. CEILING is analogous to ROUNDUP, FLOOR analogous to ROUNDDOWN. Let's imagine we want these prices to all be multiples of five cents. This is going to be $9, or in some cases with a different percent $8.95, that sort of thing. So let's make a change here. I'll double-click and use the function called MROUND, left parenthesis, after the B2, comma, .05. So then it will be, in this case, $9, and that calculation to the right, of course, makes sense now, multiplying it by 10,000. I'll drag this down just across a few. The next one's going to be 10 cents. The next one will end in 10 cents. And we don't really have to have all these decimals displayed, but it certainly doesn't hurt, and at least for a while as we're testing this out, it makes sense. That'll be an even $67. Next one $53.45, so you see what's happening. Now if somehow or other we decide well let's just round this up, so we're not... things balance out, and we'll make a slight bit more money, maybe that's not the real thrust, but instead of saying MROUNDUP, there is no such function, you use the function called CEILING. Now in some of these examples the price will go up more than we're seeing right now. Remember right now they're rounded to the nearest five cents. Now we're going to go upward to the next five cents. The real result of this calculation was slightly below $9, so we're rounding up using CEILING to go to the next one. Drag this down to the next cell, that goes up to 15 cents. So some of these will change, some will not. Most of these seem to be changing. That one didn't, that one does, and so on and so on and so on. So we're using the CEILING function. And as soon as you become familiar with this, almost immediately you know it's opposite and could've guessed it probably, it's called FLOOR. So this means go down to the next five cent multiple. In this case, you see what's happening, so we could be going down that way as well, too. So we see how CEILING and FLOOR are being used. Now if we're dealing with larger numbers, there's another aspect of using these, too, that you might not have thought of. Here's the calculation for New Salary and then here, too. You might, in a real-life situation, want to round that to the nearest penny, in which case we'd use ROUND with the number two, or to the nearest whole dollar, we'd use ROUND with the number zero. But, here's a thought. What if you get paid 26 times a year, roughly twice a month, 26 payroll periods? I've seen situations where the gross amount for each pay period is exactly a whole number. So when we're increasing the salaries here, what if we were to use MROUND? Take this calculation, comma, and let's make it go up or down to the nearest $26. Now we don't want to necessarily let people know that some people could be losing as much as $13, perhaps. There we see it. These are all going to be multiples of 26. And if we wanted to make sure that this goes up to the next 26, just like we saw in the earlier example, we would make this be CEILING. And some of these will go up, some will not, I'm doing them all together by pressing Control+Enter. And if you wanted to test this, you write a formula here. Divide these by 26. Display with multiple decimal places, there we go. Copy it down the column. Yeah, those are all evenly multiplied by 26. So different uses of rounding by way of the CEILING and FLOOR functions. Think of them both as variations on the MROUND function in the same way that we have a ROUNDUP and a ROUNDOWN to correspond to the ROUND function.