Excel has specialty rounding functions CEILING.MATH and FLOOR.MATH. Think of them like a ROUNDUP version of MROUND and a ROUNDDOWN version of MROUND. Why did they not call them MROUNDUP and MROUNDDOWN? That is a great question. Today, I show you how CEILING.MATH and FLOOR.MATH work and the equivalent formulas in Power Query.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2395. Do CEILING or FLOOR in Power Query.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
For me this is really doing MROUNDUP or MROUNDOWN.
But for whatever reason it's not called MROUNDUP or down. It's called CEILING.MATH.
Right, so if you have like a number 40 and you want to round it to the nearest 25 it's always going to round out up. S 40 goes to 50.
Even 26 - just something just barely over 25 - is still going to run up to 50.
Like how many cases do we have to order?
If you need 26 and there's only 25 per case, you need 2 cases.
And going the other way to always round down FLOOR.MATH.
The number 40 to the nearest 25 will go down to the 25 and 49.999 returns 25.
But this all gets complicated when your number is negative.
If you're sitting at negative 57 and you want to go to the higher 25, what is that?
Some people would say that's negative 75 but negative 75 is less than negative 57.
So mathematicians would say the right answer is negative 50.
Now, it's interesting.
In the old days, CEILING and FLOOR went the wrong way and when they introduced CEILING.MATH and FLOOR.MATH.
They always went in the correct direction, but they added an optional mode argument that could force it to go in the wrong direction. So the CEILING.MATH of -57 we are going to the higher number which will be -50. But if you add this Mode argument it will go the wrong way.
The ceiling will go down towards negative 75. Alright. Same thing with FLOOR.MATH, you can control whether it goes down or up, depending on whether you use the mode.
Alright, so here's the here's the four functions that we have in Excel.
CEILING.MATH, CEILING.MATH with a Mode of 1, FLOOR.MATH, and FLOOR.MATH with the mode of 1.
And the question is, can we replicate those in Power Query?
And yes we can, but you're going to have to do it with a custom column.
Here for each of the four formulas in Excel, here's the equivalent formula in Power Query.
To enter these, you're going to go to the Add Column and say that you want a Custom Column.
Let's take a look at one of these I created.
You'll give it a name PQ ceiling. And =Number.RoundUp and using the Number and the Multiple. There you have it.
The Rosetta Stone of these various functions in Excel and how to do the same thing in Power Query Check out my new book Mr Excel 2021, Unmasking Excel.
Click that I in the top right hand corner for more information.
If you like these videos please down below Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments in the YouTube comments below.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
For me this is really doing MROUNDUP or MROUNDOWN.
But for whatever reason it's not called MROUNDUP or down. It's called CEILING.MATH.
Right, so if you have like a number 40 and you want to round it to the nearest 25 it's always going to round out up. S 40 goes to 50.
Even 26 - just something just barely over 25 - is still going to run up to 50.
Like how many cases do we have to order?
If you need 26 and there's only 25 per case, you need 2 cases.
And going the other way to always round down FLOOR.MATH.
The number 40 to the nearest 25 will go down to the 25 and 49.999 returns 25.
But this all gets complicated when your number is negative.
If you're sitting at negative 57 and you want to go to the higher 25, what is that?
Some people would say that's negative 75 but negative 75 is less than negative 57.
So mathematicians would say the right answer is negative 50.
Now, it's interesting.
In the old days, CEILING and FLOOR went the wrong way and when they introduced CEILING.MATH and FLOOR.MATH.
They always went in the correct direction, but they added an optional mode argument that could force it to go in the wrong direction. So the CEILING.MATH of -57 we are going to the higher number which will be -50. But if you add this Mode argument it will go the wrong way.
The ceiling will go down towards negative 75. Alright. Same thing with FLOOR.MATH, you can control whether it goes down or up, depending on whether you use the mode.
Alright, so here's the here's the four functions that we have in Excel.
CEILING.MATH, CEILING.MATH with a Mode of 1, FLOOR.MATH, and FLOOR.MATH with the mode of 1.
And the question is, can we replicate those in Power Query?
And yes we can, but you're going to have to do it with a custom column.
Here for each of the four formulas in Excel, here's the equivalent formula in Power Query.
To enter these, you're going to go to the Add Column and say that you want a Custom Column.
Let's take a look at one of these I created.
You'll give it a name PQ ceiling. And =Number.RoundUp and using the Number and the Multiple. There you have it.
The Rosetta Stone of these various functions in Excel and how to do the same thing in Power Query Check out my new book Mr Excel 2021, Unmasking Excel.
Click that I in the top right hand corner for more information.
If you like these videos please down below Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments in the YouTube comments below.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
