Join Dennis Taylor for an in-depth discussion in this video Using the CEILING and FLOOR functions for specialized rounding needs, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Using the CEILING and FLOOR functions for specialized rounding needs