Hey welcome back to the Mr. Excel netcast I am Bill Jelen.
Basic: we start out with massive amounts of data and how are we going to analyze this? Well, let’s fire up the activity table and see if we can solve this problem.
Okay, well if yesterday wasn’t bad enough—five minutes talking about this esoteric rounding thing. After I finish that up, I kind of was nervously asking, oh you 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 it correctly. If the last significant digit is a 5, 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 AST and E-29 rule says that if the last digit is a 5, you should round towards the even, the other time’s down and it make it round to an even number. So, a little bit of tweaking to that VBA code that I wrote yesterday; memorize it yesterday that VBA round function, rounds correctly but only when the precision is 0, 1, 2, 3, 4, and so on not when its negative.
So I wrote a little function here called banker round and it says: “hey we’re going to get to our evens—the number that we want to round and the precision.” The first thing I checked to do is see if the precision is greater than or equal to zero 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 so I’d 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 .1 and multiply that number by.1. Allow Microsoft to do the round using the precision of zero and hen finally divide by the .1 to get it back to the original number.
Let’s take a look at the results here. I set up a couple of different test cases. Here’s 5.15 and 5.25 rounding it to 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 a 5.15, 15 and 5.5.15, 25; 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 banker’s round says that 5 is going to round towards the even digits do the choices are either 0-0 or 1-0. The 0 in 0-0 is winning. Now, 15 that round up towards the even digits again the choices are round to 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 and that works to the nearest hundred, the 50 rounds down to 0, 150 rounds up to 200.
So there you have it a simple little function naming lines of code although it certainly runs slower than the real round function but you are going to have a million of these but if you need to do this AST and -29 rounding; adding this function to your project would allow that to happen. I want to thank you for stopping by; we’ll see you next time for another net cast from Mr. Excel.
Well thanks for stopping by. We’ll see you next time for another net cast by Mr. Excel.
Transcription by:
Scribe4you Transcription Services