Round Up to the Next 20
April 17, 2018 - by Bill Jelen
Sandy works for a construction company. They sell flooring in packages of 20 Board Feet. When their estimators go to a job, they might say that they need 441 board feet of material. Sandy needs a way to always round up to the next 20.
As I was trying to solve this at one of my live Power Excel seminars, several functions flashed through my head:
The simplest rounding function is ROUND(Number to Round,Digits). To round something to dollars and cents, you would use =ROUND(A2,2). To round to whole dollars, you could use =ROUND(A2,0). What most people don't realize is that you can use ROUND to round to the nearest hundred or thousand. Instead of rounding to 2 digits, you could round to -2 digits and you would get the number in A2 rounded to the nearest 100. Or, =ROUND(A2,-3) would round to the nearest thousand.
Note
If you are simply trying to display numbers in thousands, don't use ROUND. Instead, use a custom number format of #,##0,K
There are other rounding functions. ROUNDUP is great for killing weeds in your driveway, but also to round up to next 1, 10, 100. =ROUND(1.23,0) is $1. But =ROUNDUP(1.23,0) is $2. You could always round up to the next 100 with =ROUNDUP(A2,-2). But you can't specify to round up to the next 20.
Another possible function is MROUND. It will round to the nearest multiple of 20. But 0.1 to 10 rounds to 0 and 10.1 to 19.9 rounds to 20. There is not an MROUNDUP, which is what we need.
Eventually, in the video, I come around to =CEILING.MATH(A2,20). For positive numbers, CEILING and CEILING.MATH both round up to the next multiple of 20.
If you had to round down to the previous 20, you could use =FLOOR.MATH(A2,20).
Also - watch the end of the video for an out-take rant about the unusual EVEN function.
Watch Video
Video Transcript
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.
Title Photo: Hugh Charles Anderson on Unsplash
