If you have to round 0.5, do you think it should round to 0 or 1? I bet that you learned in school that it should round to 1. However, this will introduce errors. Episode 1046 talks about the errors introduced by this rounding.
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 amount of Data.
How we're gonna analyze as well. Let's fire up a pivot table.
You can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Boy! The question that came in today s from another Bill and Mike Girvin and I went around and around and around about this and finally decided.
There was only one good way to do it.
So, it wasn't a dueling Excel podcast.
We're talking on something called ASTM E29.
Which might also be known as Bankers Rounding.
Which I call Ron Luther Rounding.
Now, why do I call it Ron Luther Rounding?
Because well! It was 10 or 15 years ago when a co-worker of mine, Ron Luther said, you know, "Excel doesn't round correctly." I'm like, what do you mean?
It doesn't round correctly.
He says if you have a number like 45.5.
Excel is always going to round the point 5 up to 46.
I said of course, that's the way we learned in school.
That's how you wrap, point four (.4) and below wrap down point 5 (.5) and above rounds up.
He says, "Well, that's going to introduce errors." And I really just scoffed at Ron because well frankly there was no good way to do this in Excel.
But, I got a note from Bill another Bill, and he said you know, "We have to use this ASTM E 29 rounding because it really does introduce errors, and so finally I sat down and I said, "Alright!
We're going to test this." We're gonna see if it really introduces errors and here I put in just a million.
Basically, cells that were land between 100 and 99 divided by 10.
So, I'm getting some decimals that are, .1, .2, .3, .4, .5 and so on and then over here in column C.
Just use the regular round function in Excel.
Which of course is going to take all the point five (.5) and round them up.
So, then I added up the total column of original numbers and the toll call of rounded numbers.
And you know, the law of large number says this all should round basically the same thing.
Alright! But, sure enough.
There is a bias there.
It's $52000, higher in the Random Column, which you know isn't huge, but is a point oh nine (.09) percent.
So, 91 hundreds of a percent error.
That's being introduced.
Now Bill had introduced a possible formula, checks to see if the mod of the original number is equal exactly 2.5.
And if it is then we're going to use MROUND.
MROUND says, hey!
We gonna round up to the nearest increment of 2.
So, the rule here and this is basically what Ron said is, every other number you have to round either up or down.
The ASTM E 29 says look we're going to make a simple rule.
If it's exactly point 5, we're going to round to the even number.
So, 0.5 wraps to 0.
1.5 wraps to 2, and that that alternating rounding up or down 4.5, will reduce the error.
So, I copied that down and did the same test, added everything up and turn off that error that was 52,000 is now only 572.
And instead of being 9107 percent is you know, incredibly small.
So, I said, "Alright! Well, that's weird." It does actually make sense.
If you're rounding really matters, and you want it to round up basically, the same number.
It is important to do this so using Bill's formula.
Works for numbers that are rounding to an integer.
Now, what's interesting is, Microsoft claims that parts of Excel will confirm to ASTM E 29, but clearly not this function.
You know, they don't have control over this.
This function was written in the days of VisiCalc or Lotus and Microsoft had to copy that functionality over that way, people's spreadsheets would work or when they switch from the Lotus\ or VisiCalc up to Excel.
I get that they weren't in control.
But, what's really interesting is, I'm going to switch over here to VBA.
And in VBA, there's a function called Round and this apparently is where the ASTM E-29 is working because if you round in VBA.
Well sure enough it rounds towards the even number, when it is exactly 0.5.
So, here I use this little function called VBA ROUND and copy that down and we get the exact same error.
So, very interesting you know, usually when we're rounding.
I just always use round comma 0 and I figure it's close enough.
But in fact, if you're really worried about this over a large data set.
There are certainly other ways to go, either the custom function here in VBA.
The VBA Round or using this IF function, that Bill set in.
Now, of course the inherent problem with this is the round function in Excel.
Well, i use to specify comma 1, comma 2, comma 3.
You know, to specify that we're going to tens hundreds or thousands.
But, it also lets you specify negative one, which says round to 10 and the round function and VBA can not handle rounding to negative digits.
So, you're going to run into a problem there.
You're going to have to rewrite this formula to round to the right precision and if you needed a function that would round to any precision kind of like the round function in Excel.
You're going to have a horrendous formula and probably at that point it's best to go over and create an UDF.
The UDF, of course for if you're writing to [ zero ] [ one ] [ two ] [ three ] [ four ] decimal places, go ahead and use the round function.
But, otherwise if you need to round of tens, or hundreds or thousands.
And you're worried about this being able to write up to the other area.
You're going to come up with your own algorithm.
So, there you have it a very esoteric.
But, really interesting discussion about rounding it.
When, I first heard this again 15 years ago from my buddy Ron.
He's like. I was like Ron. This can't possibly matter.
But, when you actually look, it does introduce quite a bit of error having that point five, always round up like we done in school.
Hey! There you have it.
Want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Thank you for stopping by. I will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of Data.
How we're gonna analyze as well. Let's fire up a pivot table.
You can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Boy! The question that came in today s from another Bill and Mike Girvin and I went around and around and around about this and finally decided.
There was only one good way to do it.
So, it wasn't a dueling Excel podcast.
We're talking on something called ASTM E29.
Which might also be known as Bankers Rounding.
Which I call Ron Luther Rounding.
Now, why do I call it Ron Luther Rounding?
Because well! It was 10 or 15 years ago when a co-worker of mine, Ron Luther said, you know, "Excel doesn't round correctly." I'm like, what do you mean?
It doesn't round correctly.
He says if you have a number like 45.5.
Excel is always going to round the point 5 up to 46.
I said of course, that's the way we learned in school.
That's how you wrap, point four (.4) and below wrap down point 5 (.5) and above rounds up.
He says, "Well, that's going to introduce errors." And I really just scoffed at Ron because well frankly there was no good way to do this in Excel.
But, I got a note from Bill another Bill, and he said you know, "We have to use this ASTM E 29 rounding because it really does introduce errors, and so finally I sat down and I said, "Alright!
We're going to test this." We're gonna see if it really introduces errors and here I put in just a million.
Basically, cells that were land between 100 and 99 divided by 10.
So, I'm getting some decimals that are, .1, .2, .3, .4, .5 and so on and then over here in column C.
Just use the regular round function in Excel.
Which of course is going to take all the point five (.5) and round them up.
So, then I added up the total column of original numbers and the toll call of rounded numbers.
And you know, the law of large number says this all should round basically the same thing.
Alright! But, sure enough.
There is a bias there.
It's $52000, higher in the Random Column, which you know isn't huge, but is a point oh nine (.09) percent.
So, 91 hundreds of a percent error.
That's being introduced.
Now Bill had introduced a possible formula, checks to see if the mod of the original number is equal exactly 2.5.
And if it is then we're going to use MROUND.
MROUND says, hey!
We gonna round up to the nearest increment of 2.
So, the rule here and this is basically what Ron said is, every other number you have to round either up or down.
The ASTM E 29 says look we're going to make a simple rule.
If it's exactly point 5, we're going to round to the even number.
So, 0.5 wraps to 0.
1.5 wraps to 2, and that that alternating rounding up or down 4.5, will reduce the error.
So, I copied that down and did the same test, added everything up and turn off that error that was 52,000 is now only 572.
And instead of being 9107 percent is you know, incredibly small.
So, I said, "Alright! Well, that's weird." It does actually make sense.
If you're rounding really matters, and you want it to round up basically, the same number.
It is important to do this so using Bill's formula.
Works for numbers that are rounding to an integer.
Now, what's interesting is, Microsoft claims that parts of Excel will confirm to ASTM E 29, but clearly not this function.
You know, they don't have control over this.
This function was written in the days of VisiCalc or Lotus and Microsoft had to copy that functionality over that way, people's spreadsheets would work or when they switch from the Lotus\ or VisiCalc up to Excel.
I get that they weren't in control.
But, what's really interesting is, I'm going to switch over here to VBA.
And in VBA, there's a function called Round and this apparently is where the ASTM E-29 is working because if you round in VBA.
Well sure enough it rounds towards the even number, when it is exactly 0.5.
So, here I use this little function called VBA ROUND and copy that down and we get the exact same error.
So, very interesting you know, usually when we're rounding.
I just always use round comma 0 and I figure it's close enough.
But in fact, if you're really worried about this over a large data set.
There are certainly other ways to go, either the custom function here in VBA.
The VBA Round or using this IF function, that Bill set in.
Now, of course the inherent problem with this is the round function in Excel.
Well, i use to specify comma 1, comma 2, comma 3.
You know, to specify that we're going to tens hundreds or thousands.
But, it also lets you specify negative one, which says round to 10 and the round function and VBA can not handle rounding to negative digits.
So, you're going to run into a problem there.
You're going to have to rewrite this formula to round to the right precision and if you needed a function that would round to any precision kind of like the round function in Excel.
You're going to have a horrendous formula and probably at that point it's best to go over and create an UDF.
The UDF, of course for if you're writing to [ zero ] [ one ] [ two ] [ three ] [ four ] decimal places, go ahead and use the round function.
But, otherwise if you need to round of tens, or hundreds or thousands.
And you're worried about this being able to write up to the other area.
You're going to come up with your own algorithm.
So, there you have it a very esoteric.
But, really interesting discussion about rounding it.
When, I first heard this again 15 years ago from my buddy Ron.
He's like. I was like Ron. This can't possibly matter.
But, when you actually look, it does introduce quite a bit of error having that point five, always round up like we done in school.
Hey! There you have it.
Want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Thank you for stopping by. I will see you next time for another netcast from MrExcel.
