ASTM E29 Rounding - 1046 - Learn Excel from MrExcel Podcast Video

ASTM E29 Rounding - 1046 - Learn Excel from MrExcel Podcast

Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Face it, you start out with massive amount of data, say, how are you gonna analyze this, well, let’s fire up activity table and see if you can solve this problem. Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen, boy, the question that came in today is from another Bill and Mike Girvin and I went around and round and round about this, finally decided that there was only one good way to do it, so it wasn’t a dueling Excel podcast. Let me tell you about something called ASTM E29, which might also be known as Banker’s 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 said, if you have a number like 45.5, Excel 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 round, point four and below rounds down, point five and above rounds up. He says, well that’s gonna introduced errors. And I really just scuff at Ron, because, well frankly there was no good way to do this in Excel, but I gonna note from Bill, another Bill, and he said, you know, we have to use this ASTM E29 Rounding because it really doesn’t introduced errors. And so, finally I sat down, and I sort of like, we’re gonna test this, we’re gonna see if it really introduces errors. And here I put in a, just a, a million, basically cells that were ran between 100 and 999 divided by 10. So, I’m getting some decimals that are point one, point two, point three, point four, point five, and so on. And then over here, in column C, just used a regular round function in Excel, which of course are gonna take all the point fives and round them up. So then, I added up the total of original numbers and the total column of rounded numbers and, you know, the law of large numbers says this all should round up basically the same thing, alright. But, sure enough there is a bias there, it’s 52,000 dollars higher in the rounding column, which, you know, isn’t huge, but it’s a point 09 percent, so, 9/100 of a percent error that’s being introduced. Now Bill had introduced a possible formula, check to see if the amount of the original number is equal exactly the point five, and if it is, then we’re going to use M round. M round says, hey, we’re gonna round up to the nearest increment of two, so the rule here, and this is basically what Ron said, said every other number, you have to round either up or down. The ASTM E29 says, look, we’re gonna make a simple rule, if it’s exactly point five, we’re gonna round to the even number. So, 0.5 rounds to zero, 1.5 rounds to 2, and that that alternating rounding up or down for point five will reduce the error. So I copied that down and did the same test, add everything up, and sure enough, that error that was 52,000 is now only 572. And instead of being 9/100 of a percent, is, you know, incredibly small. So, I said, oh right, well, that’s weird, it does actually make sense, if you’re rounding really matters and you wanted to round to basically the same number, it is important to do this. So, using, using Bill’s formula works for numbers that are rounding to an integer. Now, what’s interesting is Microsoft claims that part of Excel will conform to ASTM E29, but clearly not this function. You know, they don’t have control over this, this function was written in the days of Visical Lotus and Microsoft had to copy that functionality over so that way, people’s spreadsheets would work, or when they switch from Lotus or Visical up to Excel. I get that, they weren’t in control, but what’s really interesting is, I’m gonna switch over here to VVA, and in VVA there’s a function called round. And this apparently is where the ASTM E29 is working, because if you round in VVA, well, sure enough it rounds towards the even number when it is exactly 0.5. So here I used this little function called VVA round, and copy that down and we get the exact same error. So, very interesting, and you know, usually when we’re rounding, I just always use round-comma-zero, and I figure it’s close enough, but in fact, if you really worried about this over a large data set, there are certainly other ways to go, either the custom function here in VVA, the VVA round, or using this if function that Bill set in. Now, of course the apparent problem with this is the round function in Excel, well I just specify comma one, comma two, comma three, you know, to specify that we’re going to tenths, hundredths or thousandths, but also lets you specify negative one, which say round to tenths and the round function in VVA cannot handle rounding to negative digits. So you’re gonna run into a problem there, you gonna have to rewrite this formula to round to the right precision. And if you needed a function that would round to any position, kind of the round function in Excel, you’re going to have a horrendous formula. Probably at that point, it’s best to go over and create a UDF. UDF, of course for, if you’re writing the zero, one, two, three, four decimal places, go ahead and use the round function, but otherwise, if you need to round to tens to a hundreds of a thousands and you’re worried about this being able to round up to the other area, you’re gonna have to come up with your own algorithm. So there you have it and, very esoteric, but really interesting discussion about rounding. And when I first heard this, again, 15 years ago from my buddy Ron, he is like, guys oh Ron this can't possibly matter, but when you actually look, it does introduced quite a bit error, having that point five always round up, like we were thought in school. Hey, there you have it, I wanna thank you for stopping by, I’ll see you next time for another netcast from Mr. Excel.

No Links were listed yet. Go ahead and share!

Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Face it, you start out with massive amount of data, say, how are you gonna analyze this, well, let’s fire up activity table and see if you can solve this problem. Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen, boy, the question that came in today is from another Bill and Mike Girvin and I went around and round and round about this, finally decided that there was only one good way to do it, so it wasn’t a dueling Excel podcast. Let me tell you about something called ASTM E29, which might also be known as Banker’s 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 said, if you have a number like 45.5, Excel 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 round, point four and below rounds down, point five and above rounds up. He says, well that’s gonna introduced errors. And I really just scuff at Ron, because, well frankly there was no good way to do this in Excel, but I gonna note from Bill, another Bill, and he said, you know, we have to use this ASTM E29 Rounding because it really doesn’t introduced errors. And so, finally I sat down, and I sort of like, we’re gonna test this, we’re gonna see if it really introduces errors. And here I put in a, just a, a million, basically cells that were ran between 100 and 999 divided by 10. So, I’m getting some decimals that are point one, point two, point three, point four, point five, and so on. And then over here, in column C, just used a regular round function in Excel, which of course are gonna take all the point fives and round them up. So then, I added up the total of original numbers and the total column of rounded numbers and, you know, the law of large numbers says this all should round up basically the same thing, alright. But, sure enough there is a bias there, it’s 52,000 dollars higher in the rounding column, which, you know, isn’t huge, but it’s a point 09 percent, so, 9/100 of a percent error that’s being introduced. Now Bill had introduced a possible formula, check to see if the amount of the original number is equal exactly the point five, and if it is, then we’re going to use M round. M round says, hey, we’re gonna round up to the nearest increment of two, so the rule here, and this is basically what Ron said, said every other number, you have to round either up or down. The ASTM E29 says, look, we’re gonna make a simple rule, if it’s exactly point five, we’re gonna round to the even number. So, 0.5 rounds to zero, 1.5 rounds to 2, and that that alternating rounding up or down for point five will reduce the error. So I copied that down and did the same test, add everything up, and sure enough, that error that was 52,000 is now only 572. And instead of being 9/100 of a percent, is, you know, incredibly small. So, I said, oh right, well, that’s weird, it does actually make sense, if you’re rounding really matters and you wanted to round to basically the same number, it is important to do this. So, using, using Bill’s formula works for numbers that are rounding to an integer. Now, what’s interesting is Microsoft claims that part of Excel will conform to ASTM E29, but clearly not this function. You know, they don’t have control over this, this function was written in the days of Visical Lotus and Microsoft had to copy that functionality over so that way, people’s spreadsheets would work, or when they switch from Lotus or Visical up to Excel. I get that, they weren’t in control, but what’s really interesting is, I’m gonna switch over here to VVA, and in VVA there’s a function called round. And this apparently is where the ASTM E29 is working, because if you round in VVA, well, sure enough it rounds towards the even number when it is exactly 0.5. So here I used this little function called VVA round, and copy that down and we get the exact same error. So, very interesting, and you know, usually when we’re rounding, I just always use round-comma-zero, and I figure it’s close enough, but in fact, if you really worried about this over a large data set, there are certainly other ways to go, either the custom function here in VVA, the VVA round, or using this if function that Bill set in. Now, of course the apparent problem with this is the round function in Excel, well I just specify comma one, comma two, comma three, you know, to specify that we’re going to tenths, hundredths or thousandths, but also lets you specify negative one, which say round to tenths and the round function in VVA cannot handle rounding to negative digits. So you’re gonna run into a problem there, you gonna have to rewrite this formula to round to the right precision. And if you needed a function that would round to any position, kind of the round function in Excel, you’re going to have a horrendous formula. Probably at that point, it’s best to go over and create a UDF. UDF, of course for, if you’re writing the zero, one, two, three, four decimal places, go ahead and use the round function, but otherwise, if you need to round to tens to a hundreds of a thousands and you’re worried about this being able to round up to the other area, you’re gonna have to come up with your own algorithm. So there you have it and, very esoteric, but really interesting discussion about rounding. And when I first heard this, again, 15 years ago from my buddy Ron, he is like, guys oh Ron this can't possibly matter, but when you actually look, it does introduced quite a bit error, having that point five always round up, like we were thought in school. Hey, there you have it, I wanna thank you for stopping by, I’ll see you next time for another netcast from Mr. Excel.

Transcription by: Scribe4you Transcription Services