Microsoft Excel Tutorial:
A very detailed discussion today about how Excel rounds. We were all taught in school that 8.4 rounds to 8 and 8.5 rounds to 9. But not in Power Query.
Without letting you know, Power Query defaults to a rounding mode known as ASTM-E29 rounding or Banker's Rounding. In this scenario, anything ending in 5 rounds to the even number!
I am joined by Excel MVP Celia Alves today. Read Celia's great blog post on this topic at https://solveandexcel.ca/2021/03/09...the-binary-decimal-conversion-issue-in-excel/ Also subscribe to Celia' channel for a free weekly Excel course: https://www.youtube.com/channel/UCzWFp4hOnIBHHCJi_SEWV7g
Table of Contents for this video.
(0:00) Introduction
(1:00) Power Query (by Default) is Rounding Differently than Excel
(7:09) Situations where you may not want Banker's rounding
(8:12) How Power Query defaults to Banker's Rounding
(9:42) How You Can Fix the Problem by Editing the M Code in Power Query and adding an optional 3rd argument of RoundingMode.AwayFromZero to the Number.Round argument.
(12:22) Currently, the Power Query Documentation Isn't explaining this Well
(14:25) Number.ROUND(Value,2,RoundMode.AwayFromZero) doesn't even match Number.RoundAwayFromZero
(14:43) It's okay with Bill if Power Query is better than Excel, but tell people that it is different
(17:00) The 17-digit precision BUG strikes a third time, this time in Celia's workbook when Power Query reads a closed Excel file.
(23:30) Subscribe to Celia's channel for a free hour-long Excel class every week!
A very detailed discussion today about how Excel rounds. We were all taught in school that 8.4 rounds to 8 and 8.5 rounds to 9. But not in Power Query.
Without letting you know, Power Query defaults to a rounding mode known as ASTM-E29 rounding or Banker's Rounding. In this scenario, anything ending in 5 rounds to the even number!
I am joined by Excel MVP Celia Alves today. Read Celia's great blog post on this topic at https://solveandexcel.ca/2021/03/09...the-binary-decimal-conversion-issue-in-excel/ Also subscribe to Celia' channel for a free weekly Excel course: https://www.youtube.com/channel/UCzWFp4hOnIBHHCJi_SEWV7g
Table of Contents for this video.
(0:00) Introduction
(1:00) Power Query (by Default) is Rounding Differently than Excel
(7:09) Situations where you may not want Banker's rounding
(8:12) How Power Query defaults to Banker's Rounding
(9:42) How You Can Fix the Problem by Editing the M Code in Power Query and adding an optional 3rd argument of RoundingMode.AwayFromZero to the Number.Round argument.
(12:22) Currently, the Power Query Documentation Isn't explaining this Well
(14:25) Number.ROUND(Value,2,RoundMode.AwayFromZero) doesn't even match Number.RoundAwayFromZero
(14:43) It's okay with Bill if Power Query is better than Excel, but tell people that it is different
(17:00) The 17-digit precision BUG strikes a third time, this time in Celia's workbook when Power Query reads a closed Excel file.
(23:30) Subscribe to Celia's channel for a free hour-long Excel class every week!
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2392. Power Query does bankers rounding.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
A topic that we haven't talked about since 2009 but just recently came up and I have a special guest today.
I'm really excited. Excel MVP Celia Alves is here to talk about how this problem recently uncovered and what we learned about Power Query that is pretty surprising.
Hey Celia, it is great to have you here today. [ Celia: ] Thank you Bill, for inviting me.
It is a great pleasure to be here in your channel.
Thank you so much.
[ Bill: ] So now I don't know if everyone knows this, but the MVP's have this mailing list that we can all discuss problems.
It is great, the kinds of things that come up there.
I knew I had to update this video because of a conversation that you started a few days ago.
And the surprising revelation, at least is it surprising to me, about how Power Query rounds.
It doesn't match what Excel does. So tell me how this all started. What happened?
[ Celia: ] Yeah, so I already was aware of the Power Query different rounding modes available.
And the fact that the default mode rounding mode in Power Query does not match Excel’s rounding mode or rounding behavior.
And I was aware of that because of a blog post by Ken Puls.
This post was written in 2014 and I was aware of that. And I've been cautious since I became aware.
But it made us think both of us that a lot of people are not aware of this situation and so the purpose of this video is really to tell people about what Power Query is doing when rounding.
And so that people are aware and can make their choices correctly.
[ Bill: ] OK, so first let's just cover the normal way to ROUND. The way that Excel rounds.
The way that you have taught people to round as as a teacher.
What's the rule?
So let's say we have a number like 2.3 that rounds to? [ Celia: ] 2.
[ Bill: ] And 2.7 rounds to [ Celia: ] Three.
If you are rounding to the whole number. [ Bill: ] Yes, OK, but then 2.5 - right in the middle - dead even in the middle of the rule is?
[ Celia: ] 2.5? The teacher says rounds to three just because it's a convention.
It's like that. [ Bill: ] That is exactly what I learned, right?
So anything that if the last digit is 5, you always round up.
That's the rule. But what is Power Query doing?
[ Celia: ] Well, Power Query. What Power Query does is: if it is, 3.5 rounds to four. [ Bill: ] As we expect.
[ Celia: ] But if it is 4.5 it rounds to four as well.
[ Bill: ] Which is not what Excel does, right? And that that is horrible.
Now when I saw your email and just in the back of my head I was thinking I feel like I've seen this before.
When I went back and looked it was a video that I did in 2009. That was what, 12 years ago.
Someone wrote in an they had this crazy thing. They called it ASTM-E29 rounding.
Where you always round towards the even number and I had never heard of that before that day.
But back in 2009, I came up with a little formula that would do that using the MOD and IF and things like that.
Let me show this because I went back and watched the video 12 years later just to see.
[ Celia: ] And that's a very interesting video.
It's amazing how you always have a video for every situation. [ Bill: ] So yeah, I've been around so long.
I have a video for everything [ Celia ] After thousands of videos, there is always one for every situation.
[ Bill: ] But it is funny. This video never got any traction.
So it is 12 years old with only 4000 views. If you do it out, it is an average of 1 view per day, right?
So there's not a lot of people searching for how to do ASTM-E29 rounding.
If people want to do this, they're not searching for it, or at least they're not finding my video for with all those views. Alright, so what I did here with test.
And I recreated the data set so these numbers are a little bit different than the old video.
I have a million numbers in column B and those million numbers always have one digit after the decimal place, I have a total up here of those numbers. $54,994,922.
And then I did the typical Excel round =ROUND to zero decimals.
Which is doing what we were taught in school to. .5 rounds up an .4 rounds down.
And surprisingly over the course of 1,000,000 numbers. The difference of that is almost $50,000, right?
It’s not one percent, but it is nine hundredths of a percent?
So it's some fractional number, but if you have enough data, it actually is significant. Over here is the formula I used.
This MOD formula to do the banker’s rounding the for the guy.
That was the point in that video back in 2009.
And it's surprising that when you use the bankers rounding that skew, that upward skew goes away.
It actually ended up being a little bit less than the original number, and instead of nine hundredths of a percent it is ten-thousandths. It's a really small number, right?
So in theory, in this particular case, it is better.
And the fascinating thing about this whole thing.
I don't know why I thought to do this back in 2009.
Is I wrote a little VBA function =VBAROUND round that's using the ROUND function in VBA and it's doing bankers rounding already.
So for anyone who's been running VBA and using the ROUND function in VBA, we've already been getting this Banker’s rounding.
And in this in this case right here, it actually looks like it's a better way to round. Except for no one would expect it.
I took that data set and I ran it through Power Query and Power Query is doing Banker’s rounding. That's not doing any adjusting of the M.
It's just going into Power Query, Transform, clicking ROUND and saying I want to round to 0 decimal places.
So, I think that's surprising if people don't know what Banker’s rounding is, and the fact that we're getting that.
[ Celia ] Yes, so when you call it like that, “Banker’s rounding”, gives it all the sense it needs.
So if you think on a situation where you are paying out money and you do thousands or millions of transactions and you always round up by default, you end up losing money so you want to offset that a little bit.
If you do this rounding to the nearest even number an that will make you OK.
Sometimes when it's right in the middle, sometimes I go up, sometimes I go down and in the end it makes the that bias that that difference smaller, and I'm not I I don't get as many loss as much loss as I would have if I'm rounding always up.
But there are other situations where you may not want to do this kind of rounding.
For example, when I was a teacher and I was grading students, the rule in our system back there in Portugal, we grade high school students from 1 to 20.
And after at the end of the year, we grab a different set of different grades they have in different assignments.
Maybe there is an exam as well and there's a weighted average that we calculate, and then we have to round if it it gives us a decimal number.
And the rule is always if it goes to the middle .5 rounds up.
I don't want to be rounding some students up and some students down. [ Bill ] Right, yeah.
[ Celia ] Depending on if their grade is closer to even or odd.
[ Bill ] In that case, it makes no sense at all to round towards even.
They should all be consistent. Ok, so I am a Power Query rookie.
I can spell Power Query and I can entertain people for five minutes.
Can you show us how in power query we can control this and actually have it around the same way that Excel rounds? Is there a way to solve this?
[ Celia ] Yes, there's a here's a set of numbers. If we go to Data. From table slash range.
If add a new column. So here in the Add Column tab and go to rounding.
I have these three options, Rounding up, Rounding down, and Round dot dot dot.
So I'm guessing I want this one.
I am then asked how many decimal places I want to put there. And they say 2. OK.
And this is what I get. So we can maybe now Close and Load to.
Table in an existing sheet, maybe here. And we can see. Four four five.
In Excel rounds to .45, and here to .44. [ Bill ] Yes, look at that.
[ Celia ] And then for example .515 here rounds up. So in this one rounds up.
And that one rounds down.
[ Bill ] OK, So what can we do in Power Query to get it to be just like Excel? Is there a way to do that?
[ Celia ] Yes, what we can do is to come here to the formula bar.
And, after the decimal places we want to put a comma and then we discover that there's a rounding mode optional parameter that we can use.
[ Bill ] Wait a second. That wasn't in the user interface.
In the Round dialog, there was no advanced options that offered rounding mode.
How would anyone discover that?
[ Celia ] You have to find Ken Puls’ blog post, I think that's you option you have available. [ Bill ] Or now they could watch this video.
[ Celia ] So we are just trying help trying to help out Ken Puls a little bit with spreading the words because. It's like you say.
The user interface does not give us any indication that first that there is an optional parameter.
Second, it doesn't alert the user about what the default mode is.
[ Bill ] So I'm guessing, without knowing what the rounding mode options are.
If I were just a user coming along here trying to do this, I would type “RoundTheRightWayDummy”. Is that that the parameter we put in?
[ Celia ] So if you start typing it here you have… [ Bill ] So, it is not that one.
[ Celia ] If you have a Microsoft 365, you will have the intellisense, because otherwise you don't even get that.
If you are using, let's say, Excel 2016, you you will you would need to look into the documentation or some kind of book that explains this. [ Bill ] Good luck with that.
[ Celia ] Otherwise you won't be aware. So if you start typing RoundingMode dot.
And then you have these modes: Up. Down. Towards zero. Away from Zero.
And to Even is the Banker’s mode [ Bill ] And that's the default.
What a weird default.
[ Celia ] And that is not that is not explained anywhere.
So if you want to do the Excel round formula ROUND function way, you would have to use that one; AwayFromZero. Editing your formula like that.
Now 445 rounds to 45.
[ Bill ] Wow, that is not obvious or intuitive or discoverable at all. [ Celia ] No, not at all.
The function when we, the function that is generated for us in the M code when we just choose round is the Number dot Round function.
If we come here to the documentation it does not help us at all either.
It says there's an optional rounding mode parameter.
Specifies rounding direction when there is a tie between possible numbers to round to. OK.
See rounding mode dot type. There's no running mode dot type.
I looked in the library.
We want to look at the different rounding modes, so we have Up, Down, Away from zero, Towards zero, and To even.
So click away from zero. [ Bill ] Certainly they explain it there.
They don't.
So on the previous article, did they mention that they default to bankers rounding?
[ Celia ] No, [ Bill ] They don't even tell you. [ Celia ] So all these pages are blank.
There's no information about what the default is.
[ Bill ] OK, so there we are.
We're getting what they're calling Banker’s rounding by default.
And no indication from Microsoft that's happening and you would just have to be an eagle eyed person who notice that what you're getting from Excel and what you're getting from Power Query doesn't match. [ Celia ] Exactly.
If we look at all the different options we have and then comparing the results with what we get in Excel.
All the columns get some difference is the only one that does not get give us any difference.
So the only one that gives us a complete match with what we get in Excel is when we use away from zero, so that's the mode that matches it.
[ Bill ] You know my problem with that is, I'm never going to remember “Away from Zero”.
I'm going to come back to this video every time I want to remember that it's “Away from Zero”.
[ Celia ] And that's why we are doing this the same way like I said I went to Ken’s blog post. What is that again that I need to use?
[ Bill ] It's not obvious.
if they had simply called it the rounding mode of “The right way” or “The way Excel does it” or something like that.
Just make it be the default.
[ Celia ] The other the other function I was telling you about is number dot around round away from zero. So that that one is rounding away from zero. That's that's what it does.
So it's not matching that one.
If you have a negative number, [ Bill ] By default, they are doing the Banker’s rounding, which most people probably are not expecting it to do. It's not matching what Excel is doing.
And I'm not sure that I have a huge problem with that, because you know, Excel didn't write this.
The ROUND was written at Lotus or VisiCalc, and Excel just had to follow along.
And there's been cases before, like the the.
February 29th, 1900 problem where the Power Query people said, well, we're not going to replicate that just because Lotus made that error 25 years ago”.
And you can tell that in VBA, the ROUND function is using the Banker’s rounding.
So I can see lots of reasons why they might choose Banker’s rounding. But hey, they should really tell people, right?
I mean, it should be very explicit “This isn't matching Excel and we think it's better than Excel”.
[ Ceilia ] Yes, I've been thinking about it.
Maybe they thought or they assume that Power Query will be used mostly to deal with business data and then someone decided that doing their bankers rounding was the best rounding to apply.
But I mean that's just one situation.
Like we mentioned, there's so many other situations where we need to think about which rounding method to apply and and people?
I think that most people will just think of rounding as rounding up when you are in the middle in between two integer numbers. [ Bill ] That's right. Now, just a caveat here,.
I went back and watched my video from 2009 an I realized that in that data set the only choices after the decimal point - there were only 10.
It was either Point-Zero, Point 1, Point 2, 3, 4, and so on.
In real life numbers aren't like that right there.
You are going to have two or three or four decimal places.
So I reran those million calculations.
And it's funny that the skew becomes much smaller if you have real life data with two or three or four decimal places?
It's only the contrived situation they had in that video of 1,000,000 numbers that all end in .1 with a single decimal place.
So the problem that I described back in 2009 really isn't even as much of a problem. Alright, now Celia.
The real problem that you had was you knew that this rounding was wrong, but then another problem cropped up with your data. So, two problems happening. But just briefly. What was the issue there?
So what triggered all this conversation was a situation where I already knew that there were different rounding modes and I already knew that I needed to apply the AwayFromZero rounding mode, not is it?
Yes away from the rounding mode you see I I still really have to think about it too to be sure what to apply. Anyways.
I already knew that and I went to my M code and apply that extra parameter.
And loaded the data and then I took a brief look at the results and some of them were not correct.
I mean they were not matching the expected results [ Bill ] And when you mentioned that to the list.
Who is the first person to pounce on it and know what the problem was? [ Celia ] Jan Karel Piererse.
He is an expert in VBA.
He knows a lot of stuff in VBA, XML code and all that and he thought of looking into the XML code and checked the values that are stored in that code.
And so what happens is that what you type in a cell in Excel may not be exactly what is stored in the XML file.
And when you are loading the data from that Excel file from a separate Excel file using Power Query, Power Query will be looking into will be extracting what is in the XML codes and not in the cells.
[ Bill ] OK, let me let me jump in.
So, Jan Karel helped me out two years ago when I had one of the one of the people who watch my videos said hey, this is calculating incorrectly and Jan Karel looked in the XML and discovered that Excel is storing 17 digits of precision.
And since the beginning of time, the rule is that Excel only deals with 15 digits of precision, right? And the fact that they started sort storing 17 that's. That you know.
Well, maybe it's better, right? As long as they only use the first 15.
But this is the third time now in the wild that I've discovered that some functions are using all 17, which is against the rules and it's causing an incorrect calculation, right?
So congratulations, I've had two other people before you run into this and that doesn't mean that there are only three people have run into this 17 digit problem.
There's probably thousands of people that just don't realize that they're getting wrong calculations.
[ Celia ] We can talk about this maybe another opportunity, but, what's happening is that even if your number has a finite number of decimal places, for example, I can't remember the exact value that I was that I had in that situation. But if you have 1.223.
When you look what Excel stores in the XML codes, maybe 1.223000000 up to 17 digits and then the last one will be a 1.
Or it may decide to be just instead of doing this rounding up, it might be one digit less rounding down.
And who knows why and then Power Query it's not showing us that.
So if you look at the Power Query editor you will see what you had in Excel.
But then if you click in each one of the cells, below in the bar at the bottom in Power Query editor you will see the XML value that Power Query is really using instead of what we thought was the truth.
[ Bill ] Okay, and you discovered that if it's in the file, it's correct, but if you're pulling the data from a closed external file then it's going to use that extra digit. The illegal digit?
[ Celia ] Yeah, so my I haven't done a lot of testing, but from what I've tried so far, what it seems is if you are, if your query is in the same file where your data is, you won't have any problems.
Apparently that's the results of my testing so far.
But if you are pulling out the data from an Excel file from another external file, you will you will be working with the data stored in the XML code.
[ Bill ] OK, so I'm going to put a link to the video where I talk about this 15 versus 17 digits of precision.
The Excel team is well aware of this, they know that's the problem. To me. It's a violation of the prime directive.
I mean they have a flag there in their office that says “Recalc or Die”.
This is a clear case where they are not recalcing. And.
I don't know why the whole world isn't freaking out, but here we are alright.
So the moral for today is if you're using Power Query the Round function that you're getting by default isn't the same thing that we're getting in Excel.
And if your numbers really matter if that precision matters, there's a great work around, but it sure is not obvious, and it's not very well documented.
[ Celia ] And if you are extracting Excel data from other files.
And precision is really important for the calculations that you are doing.
You also need to be aware that you may be working with slightly different numbers than your actual numbers.
[ Bill ] I think in my other video, I actually suggested just rounding to 14 digits.
Take everything in and YOU your yourself round the 14 digits, as the first step, which is what Excel should be doing. Excel should be rounding to 15 digits of precision.
It's not a step we should have to take. Well, Celia, thanks for your time today.
Now, this is a fascinating topic. [ Celia ] My Pleasure.
[ Bill ] When I say this is a fascinating topic, it shows what nerds we are, right that this is a fascinating topic. But I really think that people need to know that.
[ Celia ] No, it is it is.
It is fascinating, as much as it should concern everyone using Excel because you may be doing something wrong with your data that you are not aware of, so that's the whole purpose of this video. I guess.
[ Bill ] All right now I'm going to put you on the spot here. I didn't.
I didn't ask you to prepare for this.
At the end of all my videos, I try and sell something. Do you have anything you want to promote? Any courses coming up?
[ Celia ] I will be preparing a course shortly about automation in Excel.
But if you want to be aware of that, just feel free to follow my YouTube channel Celia Alves Solve & Excel.
Every week I have a long class live there that you can participate in.
And then I leave the recording there available.
And then I have shorter videos that are extracted from those long class. [ Bill ] A free class every week? A free class?
[ Celia ] Yes, Free class. Every week. Just tackling some kind of Excel problem.
I'm mostly focusing on Excel reporting automation, so anything that can help people.
Saving time with their tasks in Excel, either with Power Query with VBA or just Excel functions or other features. So that's my focus there.
And so if you follow that you will be aware you will be aware when I come up with a course.
[ Bill ] Perfect, [ Celia ] And thank you for the opportunity.
[ Bill ] For people watching this, click that I in the top right hand corner and you can subscribe to Celia’s channel.
[ Celia ] If you are watching and not subscribed yet to Bill’s video, please go there.
It was one of my first, the first, the very first channels I found about Excel.
It's just a surprising and honor to me today that I'm participating in one of your videos go because a lot of my work in Excel started in your channel. Thank you. [ Bill ] Oh, thanks Celia. Alright normally.
So, Celia, I just had a new book that came out last week.
The MrExcel 2021 and that's the book I've been pitching, but this topic. This ASTM-E29 Rounding isn't in that new book.
It's in a book that I wrote long ago called Power Excel with MrExcel 2019 edition.
Click the I in the top right hand corner for that.
Well, Celia, I want to thank you for your time and I want to thank everyone for stopping by.
Please, down below the video, click Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below. Thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
A topic that we haven't talked about since 2009 but just recently came up and I have a special guest today.
I'm really excited. Excel MVP Celia Alves is here to talk about how this problem recently uncovered and what we learned about Power Query that is pretty surprising.
Hey Celia, it is great to have you here today. [ Celia: ] Thank you Bill, for inviting me.
It is a great pleasure to be here in your channel.
Thank you so much.
[ Bill: ] So now I don't know if everyone knows this, but the MVP's have this mailing list that we can all discuss problems.
It is great, the kinds of things that come up there.
I knew I had to update this video because of a conversation that you started a few days ago.
And the surprising revelation, at least is it surprising to me, about how Power Query rounds.
It doesn't match what Excel does. So tell me how this all started. What happened?
[ Celia: ] Yeah, so I already was aware of the Power Query different rounding modes available.
And the fact that the default mode rounding mode in Power Query does not match Excel’s rounding mode or rounding behavior.
And I was aware of that because of a blog post by Ken Puls.
This post was written in 2014 and I was aware of that. And I've been cautious since I became aware.
But it made us think both of us that a lot of people are not aware of this situation and so the purpose of this video is really to tell people about what Power Query is doing when rounding.
And so that people are aware and can make their choices correctly.
[ Bill: ] OK, so first let's just cover the normal way to ROUND. The way that Excel rounds.
The way that you have taught people to round as as a teacher.
What's the rule?
So let's say we have a number like 2.3 that rounds to? [ Celia: ] 2.
[ Bill: ] And 2.7 rounds to [ Celia: ] Three.
If you are rounding to the whole number. [ Bill: ] Yes, OK, but then 2.5 - right in the middle - dead even in the middle of the rule is?
[ Celia: ] 2.5? The teacher says rounds to three just because it's a convention.
It's like that. [ Bill: ] That is exactly what I learned, right?
So anything that if the last digit is 5, you always round up.
That's the rule. But what is Power Query doing?
[ Celia: ] Well, Power Query. What Power Query does is: if it is, 3.5 rounds to four. [ Bill: ] As we expect.
[ Celia: ] But if it is 4.5 it rounds to four as well.
[ Bill: ] Which is not what Excel does, right? And that that is horrible.
Now when I saw your email and just in the back of my head I was thinking I feel like I've seen this before.
When I went back and looked it was a video that I did in 2009. That was what, 12 years ago.
Someone wrote in an they had this crazy thing. They called it ASTM-E29 rounding.
Where you always round towards the even number and I had never heard of that before that day.
But back in 2009, I came up with a little formula that would do that using the MOD and IF and things like that.
Let me show this because I went back and watched the video 12 years later just to see.
[ Celia: ] And that's a very interesting video.
It's amazing how you always have a video for every situation. [ Bill: ] So yeah, I've been around so long.
I have a video for everything [ Celia ] After thousands of videos, there is always one for every situation.
[ Bill: ] But it is funny. This video never got any traction.
So it is 12 years old with only 4000 views. If you do it out, it is an average of 1 view per day, right?
So there's not a lot of people searching for how to do ASTM-E29 rounding.
If people want to do this, they're not searching for it, or at least they're not finding my video for with all those views. Alright, so what I did here with test.
And I recreated the data set so these numbers are a little bit different than the old video.
I have a million numbers in column B and those million numbers always have one digit after the decimal place, I have a total up here of those numbers. $54,994,922.
And then I did the typical Excel round =ROUND to zero decimals.
Which is doing what we were taught in school to. .5 rounds up an .4 rounds down.
And surprisingly over the course of 1,000,000 numbers. The difference of that is almost $50,000, right?
It’s not one percent, but it is nine hundredths of a percent?
So it's some fractional number, but if you have enough data, it actually is significant. Over here is the formula I used.
This MOD formula to do the banker’s rounding the for the guy.
That was the point in that video back in 2009.
And it's surprising that when you use the bankers rounding that skew, that upward skew goes away.
It actually ended up being a little bit less than the original number, and instead of nine hundredths of a percent it is ten-thousandths. It's a really small number, right?
So in theory, in this particular case, it is better.
And the fascinating thing about this whole thing.
I don't know why I thought to do this back in 2009.
Is I wrote a little VBA function =VBAROUND round that's using the ROUND function in VBA and it's doing bankers rounding already.
So for anyone who's been running VBA and using the ROUND function in VBA, we've already been getting this Banker’s rounding.
And in this in this case right here, it actually looks like it's a better way to round. Except for no one would expect it.
I took that data set and I ran it through Power Query and Power Query is doing Banker’s rounding. That's not doing any adjusting of the M.
It's just going into Power Query, Transform, clicking ROUND and saying I want to round to 0 decimal places.
So, I think that's surprising if people don't know what Banker’s rounding is, and the fact that we're getting that.
[ Celia ] Yes, so when you call it like that, “Banker’s rounding”, gives it all the sense it needs.
So if you think on a situation where you are paying out money and you do thousands or millions of transactions and you always round up by default, you end up losing money so you want to offset that a little bit.
If you do this rounding to the nearest even number an that will make you OK.
Sometimes when it's right in the middle, sometimes I go up, sometimes I go down and in the end it makes the that bias that that difference smaller, and I'm not I I don't get as many loss as much loss as I would have if I'm rounding always up.
But there are other situations where you may not want to do this kind of rounding.
For example, when I was a teacher and I was grading students, the rule in our system back there in Portugal, we grade high school students from 1 to 20.
And after at the end of the year, we grab a different set of different grades they have in different assignments.
Maybe there is an exam as well and there's a weighted average that we calculate, and then we have to round if it it gives us a decimal number.
And the rule is always if it goes to the middle .5 rounds up.
I don't want to be rounding some students up and some students down. [ Bill ] Right, yeah.
[ Celia ] Depending on if their grade is closer to even or odd.
[ Bill ] In that case, it makes no sense at all to round towards even.
They should all be consistent. Ok, so I am a Power Query rookie.
I can spell Power Query and I can entertain people for five minutes.
Can you show us how in power query we can control this and actually have it around the same way that Excel rounds? Is there a way to solve this?
[ Celia ] Yes, there's a here's a set of numbers. If we go to Data. From table slash range.
If add a new column. So here in the Add Column tab and go to rounding.
I have these three options, Rounding up, Rounding down, and Round dot dot dot.
So I'm guessing I want this one.
I am then asked how many decimal places I want to put there. And they say 2. OK.
And this is what I get. So we can maybe now Close and Load to.
Table in an existing sheet, maybe here. And we can see. Four four five.
In Excel rounds to .45, and here to .44. [ Bill ] Yes, look at that.
[ Celia ] And then for example .515 here rounds up. So in this one rounds up.
And that one rounds down.
[ Bill ] OK, So what can we do in Power Query to get it to be just like Excel? Is there a way to do that?
[ Celia ] Yes, what we can do is to come here to the formula bar.
And, after the decimal places we want to put a comma and then we discover that there's a rounding mode optional parameter that we can use.
[ Bill ] Wait a second. That wasn't in the user interface.
In the Round dialog, there was no advanced options that offered rounding mode.
How would anyone discover that?
[ Celia ] You have to find Ken Puls’ blog post, I think that's you option you have available. [ Bill ] Or now they could watch this video.
[ Celia ] So we are just trying help trying to help out Ken Puls a little bit with spreading the words because. It's like you say.
The user interface does not give us any indication that first that there is an optional parameter.
Second, it doesn't alert the user about what the default mode is.
[ Bill ] So I'm guessing, without knowing what the rounding mode options are.
If I were just a user coming along here trying to do this, I would type “RoundTheRightWayDummy”. Is that that the parameter we put in?
[ Celia ] So if you start typing it here you have… [ Bill ] So, it is not that one.
[ Celia ] If you have a Microsoft 365, you will have the intellisense, because otherwise you don't even get that.
If you are using, let's say, Excel 2016, you you will you would need to look into the documentation or some kind of book that explains this. [ Bill ] Good luck with that.
[ Celia ] Otherwise you won't be aware. So if you start typing RoundingMode dot.
And then you have these modes: Up. Down. Towards zero. Away from Zero.
And to Even is the Banker’s mode [ Bill ] And that's the default.
What a weird default.
[ Celia ] And that is not that is not explained anywhere.
So if you want to do the Excel round formula ROUND function way, you would have to use that one; AwayFromZero. Editing your formula like that.
Now 445 rounds to 45.
[ Bill ] Wow, that is not obvious or intuitive or discoverable at all. [ Celia ] No, not at all.
The function when we, the function that is generated for us in the M code when we just choose round is the Number dot Round function.
If we come here to the documentation it does not help us at all either.
It says there's an optional rounding mode parameter.
Specifies rounding direction when there is a tie between possible numbers to round to. OK.
See rounding mode dot type. There's no running mode dot type.
I looked in the library.
We want to look at the different rounding modes, so we have Up, Down, Away from zero, Towards zero, and To even.
So click away from zero. [ Bill ] Certainly they explain it there.
They don't.
So on the previous article, did they mention that they default to bankers rounding?
[ Celia ] No, [ Bill ] They don't even tell you. [ Celia ] So all these pages are blank.
There's no information about what the default is.
[ Bill ] OK, so there we are.
We're getting what they're calling Banker’s rounding by default.
And no indication from Microsoft that's happening and you would just have to be an eagle eyed person who notice that what you're getting from Excel and what you're getting from Power Query doesn't match. [ Celia ] Exactly.
If we look at all the different options we have and then comparing the results with what we get in Excel.
All the columns get some difference is the only one that does not get give us any difference.
So the only one that gives us a complete match with what we get in Excel is when we use away from zero, so that's the mode that matches it.
[ Bill ] You know my problem with that is, I'm never going to remember “Away from Zero”.
I'm going to come back to this video every time I want to remember that it's “Away from Zero”.
[ Celia ] And that's why we are doing this the same way like I said I went to Ken’s blog post. What is that again that I need to use?
[ Bill ] It's not obvious.
if they had simply called it the rounding mode of “The right way” or “The way Excel does it” or something like that.
Just make it be the default.
[ Celia ] The other the other function I was telling you about is number dot around round away from zero. So that that one is rounding away from zero. That's that's what it does.
So it's not matching that one.
If you have a negative number, [ Bill ] By default, they are doing the Banker’s rounding, which most people probably are not expecting it to do. It's not matching what Excel is doing.
And I'm not sure that I have a huge problem with that, because you know, Excel didn't write this.
The ROUND was written at Lotus or VisiCalc, and Excel just had to follow along.
And there's been cases before, like the the.
February 29th, 1900 problem where the Power Query people said, well, we're not going to replicate that just because Lotus made that error 25 years ago”.
And you can tell that in VBA, the ROUND function is using the Banker’s rounding.
So I can see lots of reasons why they might choose Banker’s rounding. But hey, they should really tell people, right?
I mean, it should be very explicit “This isn't matching Excel and we think it's better than Excel”.
[ Ceilia ] Yes, I've been thinking about it.
Maybe they thought or they assume that Power Query will be used mostly to deal with business data and then someone decided that doing their bankers rounding was the best rounding to apply.
But I mean that's just one situation.
Like we mentioned, there's so many other situations where we need to think about which rounding method to apply and and people?
I think that most people will just think of rounding as rounding up when you are in the middle in between two integer numbers. [ Bill ] That's right. Now, just a caveat here,.
I went back and watched my video from 2009 an I realized that in that data set the only choices after the decimal point - there were only 10.
It was either Point-Zero, Point 1, Point 2, 3, 4, and so on.
In real life numbers aren't like that right there.
You are going to have two or three or four decimal places.
So I reran those million calculations.
And it's funny that the skew becomes much smaller if you have real life data with two or three or four decimal places?
It's only the contrived situation they had in that video of 1,000,000 numbers that all end in .1 with a single decimal place.
So the problem that I described back in 2009 really isn't even as much of a problem. Alright, now Celia.
The real problem that you had was you knew that this rounding was wrong, but then another problem cropped up with your data. So, two problems happening. But just briefly. What was the issue there?
So what triggered all this conversation was a situation where I already knew that there were different rounding modes and I already knew that I needed to apply the AwayFromZero rounding mode, not is it?
Yes away from the rounding mode you see I I still really have to think about it too to be sure what to apply. Anyways.
I already knew that and I went to my M code and apply that extra parameter.
And loaded the data and then I took a brief look at the results and some of them were not correct.
I mean they were not matching the expected results [ Bill ] And when you mentioned that to the list.
Who is the first person to pounce on it and know what the problem was? [ Celia ] Jan Karel Piererse.
He is an expert in VBA.
He knows a lot of stuff in VBA, XML code and all that and he thought of looking into the XML code and checked the values that are stored in that code.
And so what happens is that what you type in a cell in Excel may not be exactly what is stored in the XML file.
And when you are loading the data from that Excel file from a separate Excel file using Power Query, Power Query will be looking into will be extracting what is in the XML codes and not in the cells.
[ Bill ] OK, let me let me jump in.
So, Jan Karel helped me out two years ago when I had one of the one of the people who watch my videos said hey, this is calculating incorrectly and Jan Karel looked in the XML and discovered that Excel is storing 17 digits of precision.
And since the beginning of time, the rule is that Excel only deals with 15 digits of precision, right? And the fact that they started sort storing 17 that's. That you know.
Well, maybe it's better, right? As long as they only use the first 15.
But this is the third time now in the wild that I've discovered that some functions are using all 17, which is against the rules and it's causing an incorrect calculation, right?
So congratulations, I've had two other people before you run into this and that doesn't mean that there are only three people have run into this 17 digit problem.
There's probably thousands of people that just don't realize that they're getting wrong calculations.
[ Celia ] We can talk about this maybe another opportunity, but, what's happening is that even if your number has a finite number of decimal places, for example, I can't remember the exact value that I was that I had in that situation. But if you have 1.223.
When you look what Excel stores in the XML codes, maybe 1.223000000 up to 17 digits and then the last one will be a 1.
Or it may decide to be just instead of doing this rounding up, it might be one digit less rounding down.
And who knows why and then Power Query it's not showing us that.
So if you look at the Power Query editor you will see what you had in Excel.
But then if you click in each one of the cells, below in the bar at the bottom in Power Query editor you will see the XML value that Power Query is really using instead of what we thought was the truth.
[ Bill ] Okay, and you discovered that if it's in the file, it's correct, but if you're pulling the data from a closed external file then it's going to use that extra digit. The illegal digit?
[ Celia ] Yeah, so my I haven't done a lot of testing, but from what I've tried so far, what it seems is if you are, if your query is in the same file where your data is, you won't have any problems.
Apparently that's the results of my testing so far.
But if you are pulling out the data from an Excel file from another external file, you will you will be working with the data stored in the XML code.
[ Bill ] OK, so I'm going to put a link to the video where I talk about this 15 versus 17 digits of precision.
The Excel team is well aware of this, they know that's the problem. To me. It's a violation of the prime directive.
I mean they have a flag there in their office that says “Recalc or Die”.
This is a clear case where they are not recalcing. And.
I don't know why the whole world isn't freaking out, but here we are alright.
So the moral for today is if you're using Power Query the Round function that you're getting by default isn't the same thing that we're getting in Excel.
And if your numbers really matter if that precision matters, there's a great work around, but it sure is not obvious, and it's not very well documented.
[ Celia ] And if you are extracting Excel data from other files.
And precision is really important for the calculations that you are doing.
You also need to be aware that you may be working with slightly different numbers than your actual numbers.
[ Bill ] I think in my other video, I actually suggested just rounding to 14 digits.
Take everything in and YOU your yourself round the 14 digits, as the first step, which is what Excel should be doing. Excel should be rounding to 15 digits of precision.
It's not a step we should have to take. Well, Celia, thanks for your time today.
Now, this is a fascinating topic. [ Celia ] My Pleasure.
[ Bill ] When I say this is a fascinating topic, it shows what nerds we are, right that this is a fascinating topic. But I really think that people need to know that.
[ Celia ] No, it is it is.
It is fascinating, as much as it should concern everyone using Excel because you may be doing something wrong with your data that you are not aware of, so that's the whole purpose of this video. I guess.
[ Bill ] All right now I'm going to put you on the spot here. I didn't.
I didn't ask you to prepare for this.
At the end of all my videos, I try and sell something. Do you have anything you want to promote? Any courses coming up?
[ Celia ] I will be preparing a course shortly about automation in Excel.
But if you want to be aware of that, just feel free to follow my YouTube channel Celia Alves Solve & Excel.
Every week I have a long class live there that you can participate in.
And then I leave the recording there available.
And then I have shorter videos that are extracted from those long class. [ Bill ] A free class every week? A free class?
[ Celia ] Yes, Free class. Every week. Just tackling some kind of Excel problem.
I'm mostly focusing on Excel reporting automation, so anything that can help people.
Saving time with their tasks in Excel, either with Power Query with VBA or just Excel functions or other features. So that's my focus there.
And so if you follow that you will be aware you will be aware when I come up with a course.
[ Bill ] Perfect, [ Celia ] And thank you for the opportunity.
[ Bill ] For people watching this, click that I in the top right hand corner and you can subscribe to Celia’s channel.
[ Celia ] If you are watching and not subscribed yet to Bill’s video, please go there.
It was one of my first, the first, the very first channels I found about Excel.
It's just a surprising and honor to me today that I'm participating in one of your videos go because a lot of my work in Excel started in your channel. Thank you. [ Bill ] Oh, thanks Celia. Alright normally.
So, Celia, I just had a new book that came out last week.
The MrExcel 2021 and that's the book I've been pitching, but this topic. This ASTM-E29 Rounding isn't in that new book.
It's in a book that I wrote long ago called Power Excel with MrExcel 2019 edition.
Click the I in the top right hand corner for that.
Well, Celia, I want to thank you for your time and I want to thank everyone for stopping by.
Please, down below the video, click Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below. Thank you for stopping by.
I will see you next time for another netcast from MrExcel.
