Is there an equivalent of MROUNDUP?
How to round up to next increment of 20?
Review of rounding tricks & techniques
ROUND(Number,Digits) and Digits can be negative to round to 10's or 100's
ROUNDUP is great for killing weeds in your driveway, but also to round up to next 1, 10, 100
MROUND will round to nearest 20, but there is not a MROUNDUP
Instead, use CEILING.MATH
The opposite function is FLOOR.MATH to MROUNDDOWN
How to round up to next increment of 20?
Review of rounding tricks & techniques
ROUND(Number,Digits) and Digits can be negative to round to 10's or 100's
ROUNDUP is great for killing weeds in your driveway, but also to round up to next 1, 10, 100
MROUND will round to nearest 20, but there is not a MROUNDUP
Instead, use CEILING.MATH
The opposite function is FLOOR.MATH to MROUNDDOWN
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2193: Round Up to the Next 20.
Great question today; I was out in California doing a seminar for a company that did a lot of work at schools, and they were ordering equipment in board feet.
Alright, and if you needed, you know-- like it came in a package of 20, 20 feet-- so if you needed 432, you would have to round up to 440 to, you know, order full case lots, right?
And they said, “How do you round to the nearest 20?” Alright.
And, you know, I have a lot of rounding tricks here that I know really well; like, for example, =ROUND of this number to 2 digits.
That works great-- I understand how that works.
And, also, I want to point out here at this awesome trick: did you know that that =ROUND will let you round to the nearest, you know, two digits or one digit, but also to the nearest 10 or nearest 100 by using negatives?
That's cool, right?
I love that.
You know, so, was there some way to get this to round to 20?
No, there's not.
Alright.
So we have ROUND and-- ROUND-- anything under 0.5 will round down and 0.5 and up rounds up.
ROUNDUP, though, says, "Hey, I don't care what it is we're always going to go to the next integer;" well, ROUNDDOWN says we're always going to go down.
But those aren't going to solve our problem.
So then I thought about the MROUND function-- Multiple Round-- and this is cool.
You say that you want to round that 8 to the nearest 20, and so, you know, 8 rounds down but 11, 15, 19, rounds up.
So I was wondering, you know, is there a way that I could do MROUND of that number, plus 9.999, so that way it'll always round up?
But, then I started thinking about back to, you know, a book series I used to write for Q-- the heaviest Excel book ever, 7 pounds of Excel with a list of all 455 functions and a real-life household use for those functions-- and as I started going back through those functions that I hardly ever use, I realized the answer.
This is called =CEILING.
CEILING-- =CEILING.MATH.
So we want to round 8 to the nearest 20, getting to specify the significance.
We'll make sure that it always rounds up to the next higher 20.
And if we ask for exactly 20, it doesn't round up from there-- it stays.
CEILING.MATH is the new version, CEILING is the old version.
For positive numbers they operate exactly the same-- doesn't matter which one you use.
So, a cool trick there.
I'm not even sure that old book with the seven pounds of Excel is even in print.
I certainly don't have a screenshot of it.
That was Excel 2013 In Depth --heaviest Excel book ever.
If you're looking for something a little bit lighter with a lot of great tips, MrExcel LIVe, The 54 Greatest Tips of All Time.
Wrap-up: How to round up to the next increment of 20.
And I show you some rounding tricks and techniques.
NOUND(Number,Digits), and the digits can be negative, rounded tens or hundreds, I love that trick; ROUNDUP, great for killing weeds in your driveway, but it also ran up to the next one or 10 or 100; MROUND, will round to the nearest 20 but there's no MROUNDUP.
Instead we're going to use a CEILING.MATH.
There's also a FLOOR, which I guess would be like ROUND-- MROUNDDOWN.
FLOOR.MATH will go down-- down to the previous increment of 20.
Well, hey I want to think the folks in California who asked me that question.
I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, let me throw an outtake in here today.
Reminiscing about that great old book with seven pounds of Excel.
One of the most bizarre functions I found in there is a function called EVEN--EVEN.
This must be left over from the days of, like, Busy Calc or Lotus or something like that.
EVEN will take a number and it'll round it up to the next even number.
So, for 4, the EVEN of 4 is 4.
But 3, the EVEN of 3 is 4.
This is so bizarre-- why is this there?
Is it like if you were selling 7 pound Excel books and they came back to a case and you need to figure out how many cases?
I mean, certainly we can do this with an MROUND.
I don't know, it's just bizarre.
Great question today; I was out in California doing a seminar for a company that did a lot of work at schools, and they were ordering equipment in board feet.
Alright, and if you needed, you know-- like it came in a package of 20, 20 feet-- so if you needed 432, you would have to round up to 440 to, you know, order full case lots, right?
And they said, “How do you round to the nearest 20?” Alright.
And, you know, I have a lot of rounding tricks here that I know really well; like, for example, =ROUND of this number to 2 digits.
That works great-- I understand how that works.
And, also, I want to point out here at this awesome trick: did you know that that =ROUND will let you round to the nearest, you know, two digits or one digit, but also to the nearest 10 or nearest 100 by using negatives?
That's cool, right?
I love that.
You know, so, was there some way to get this to round to 20?
No, there's not.
Alright.
So we have ROUND and-- ROUND-- anything under 0.5 will round down and 0.5 and up rounds up.
ROUNDUP, though, says, "Hey, I don't care what it is we're always going to go to the next integer;" well, ROUNDDOWN says we're always going to go down.
But those aren't going to solve our problem.
So then I thought about the MROUND function-- Multiple Round-- and this is cool.
You say that you want to round that 8 to the nearest 20, and so, you know, 8 rounds down but 11, 15, 19, rounds up.
So I was wondering, you know, is there a way that I could do MROUND of that number, plus 9.999, so that way it'll always round up?
But, then I started thinking about back to, you know, a book series I used to write for Q-- the heaviest Excel book ever, 7 pounds of Excel with a list of all 455 functions and a real-life household use for those functions-- and as I started going back through those functions that I hardly ever use, I realized the answer.
This is called =CEILING.
CEILING-- =CEILING.MATH.
So we want to round 8 to the nearest 20, getting to specify the significance.
We'll make sure that it always rounds up to the next higher 20.
And if we ask for exactly 20, it doesn't round up from there-- it stays.
CEILING.MATH is the new version, CEILING is the old version.
For positive numbers they operate exactly the same-- doesn't matter which one you use.
So, a cool trick there.
I'm not even sure that old book with the seven pounds of Excel is even in print.
I certainly don't have a screenshot of it.
That was Excel 2013 In Depth --heaviest Excel book ever.
If you're looking for something a little bit lighter with a lot of great tips, MrExcel LIVe, The 54 Greatest Tips of All Time.
Wrap-up: How to round up to the next increment of 20.
And I show you some rounding tricks and techniques.
NOUND(Number,Digits), and the digits can be negative, rounded tens or hundreds, I love that trick; ROUNDUP, great for killing weeds in your driveway, but it also ran up to the next one or 10 or 100; MROUND, will round to the nearest 20 but there's no MROUNDUP.
Instead we're going to use a CEILING.MATH.
There's also a FLOOR, which I guess would be like ROUND-- MROUNDDOWN.
FLOOR.MATH will go down-- down to the previous increment of 20.
Well, hey I want to think the folks in California who asked me that question.
I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, let me throw an outtake in here today.
Reminiscing about that great old book with seven pounds of Excel.
One of the most bizarre functions I found in there is a function called EVEN--EVEN.
This must be left over from the days of, like, Busy Calc or Lotus or something like that.
EVEN will take a number and it'll round it up to the next even number.
So, for 4, the EVEN of 4 is 4.
But 3, the EVEN of 3 is 4.
This is so bizarre-- why is this there?
Is it like if you were selling 7 pound Excel books and they came back to a case and you need to figure out how many cases?
I mean, certainly we can do this with an MROUND.
I don't know, it's just bizarre.
