After yesterdays podcast about ASTM E29 rounding, I produce a function in VBA that will correctly do the bankers rounding algorithm in Excel. Episode 1047 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey Welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically, We start out With massive Amounts of Data.
So how we're going to analyze this, well let's fire up a pivot table lets see if you Can solve this Problem.
Okay, well if Yesterday wasn't bad Enough, five minutes talking about this esoteric rounding thing.
After I finished that up you know I kind of was nebulous. The answer you know You're Have to Rewrite your own Algorithm.
I said all right that can't be that Tough to do?
We were talking about how to round correctly if the last significant digit is a five.
In school we were Always taught to round that up to the next whole number but Yesterday go ahead and watch yesterday's podcast for the painful details.
I showed how that actually Introduces a bit of bias.
and the ASTM E 29 rule says that if the last digit is a five you Should round towards the even.
The other times down and they make it round to the even number, so a little bit of tweaking to that VBA code that I wrote Yesterday.
Remember, I said Yesterday the VBA round function rounds correctly but only when the Precision Is 0, 1, 2, 3, 4, and so on not when it's negative so I wrote a little Function here Called BankerRound And it Says hey, we're Going to get two arguments.
the number we went around and the Precision.
The first thing I check to do is see if the precision is greater than or equal to 0 and at that point I just turn it over to microsoft and let them use the VBA round function which does it correctly.
Otherwise, that's when I need to write my own code.
I change the number by basically making it smaller.
so if we ask for precision of minus 1.
10 to the Minus 1 Is the same as point 1.
I multiply that Number by Point 1 allow microsoft to do the round using the Precision of 0 and then finally, divide by the Point 1 to get it back to the original number.
Let's Take a look at the results here.
I set up a couple of dfferent test cases.
Here's 5.15 and 5.25 we rounding it at one decimal place and both of those round to 5.2.
So the 5.15 is rounding up towards the even number the 5.25 is rounding down towards the even number.
Here 5.1515 and 5.1525. Those both round to the even number 5.152.
Now the thing I had yesterday, just using the VBA round function would work Here are the real test where we have 5 and 15 and rounding that to the nearest 10.
Well you know in School, 5 would round up to 10 that's the way that I learned but the BankersRound says that 5 is going round towards the even digit.
So the Choices are either, 00 Or 1 0. The 0 in 00 is winning.
Now 15 though rounds up towards the even digit.
Again the choices are round the 10 or round to 20 because 20 is the even digit.
The 2. It Rounds towards the 20 and then here 50 and 150 rounding to the minus 2 in other words to the nearest hundred the 50 rounds down to 0. 150 rounds up to 200.
So there you have it a simple little function.
Not Many lines of Code.
Although it certainly run slower than the real round function, particularly when you have a million of these but you Know if you need to do this ASTM E 29 rounding adding this function to your project would allow that to happen.
Well, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Basically, We start out With massive Amounts of Data.
So how we're going to analyze this, well let's fire up a pivot table lets see if you Can solve this Problem.
Okay, well if Yesterday wasn't bad Enough, five minutes talking about this esoteric rounding thing.
After I finished that up you know I kind of was nebulous. The answer you know You're Have to Rewrite your own Algorithm.
I said all right that can't be that Tough to do?
We were talking about how to round correctly if the last significant digit is a five.
In school we were Always taught to round that up to the next whole number but Yesterday go ahead and watch yesterday's podcast for the painful details.
I showed how that actually Introduces a bit of bias.
and the ASTM E 29 rule says that if the last digit is a five you Should round towards the even.
The other times down and they make it round to the even number, so a little bit of tweaking to that VBA code that I wrote Yesterday.
Remember, I said Yesterday the VBA round function rounds correctly but only when the Precision Is 0, 1, 2, 3, 4, and so on not when it's negative so I wrote a little Function here Called BankerRound And it Says hey, we're Going to get two arguments.
the number we went around and the Precision.
The first thing I check to do is see if the precision is greater than or equal to 0 and at that point I just turn it over to microsoft and let them use the VBA round function which does it correctly.
Otherwise, that's when I need to write my own code.
I change the number by basically making it smaller.
so if we ask for precision of minus 1.
10 to the Minus 1 Is the same as point 1.
I multiply that Number by Point 1 allow microsoft to do the round using the Precision of 0 and then finally, divide by the Point 1 to get it back to the original number.
Let's Take a look at the results here.
I set up a couple of dfferent test cases.
Here's 5.15 and 5.25 we rounding it at one decimal place and both of those round to 5.2.
So the 5.15 is rounding up towards the even number the 5.25 is rounding down towards the even number.
Here 5.1515 and 5.1525. Those both round to the even number 5.152.
Now the thing I had yesterday, just using the VBA round function would work Here are the real test where we have 5 and 15 and rounding that to the nearest 10.
Well you know in School, 5 would round up to 10 that's the way that I learned but the BankersRound says that 5 is going round towards the even digit.
So the Choices are either, 00 Or 1 0. The 0 in 00 is winning.
Now 15 though rounds up towards the even digit.
Again the choices are round the 10 or round to 20 because 20 is the even digit.
The 2. It Rounds towards the 20 and then here 50 and 150 rounding to the minus 2 in other words to the nearest hundred the 50 rounds down to 0. 150 rounds up to 200.
So there you have it a simple little function.
Not Many lines of Code.
Although it certainly run slower than the real round function, particularly when you have a million of these but you Know if you need to do this ASTM E 29 rounding adding this function to your project would allow that to happen.
Well, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
