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.
Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Well, we have to visit this rounding question again, the question sent in by Gopal. Gopal is in India and is dealing with a new ruling by the sixth Indian pay commission that says,hey, anything in excess of $10.00 up to and including the decimal 0.99 should be rounded down to the last $10.00. But once you get above that, once you get to, you know, like for example 21 dollars, it should be rounded up to the next 10. And so, you know, none of the Excel’s built in rounding functions are going to deal with that. I’m going to break it down. I’m going to use a cool function here called mod.
Now, I know that mod takes a number and divides it by a divisor and then reports the remainder. So for example, 24 mod 20 will report four. I didn’t know what was going happen when we had decimals and so I was kind of happy to see that it did actually report the decimal portion. And what’s cool here is it basically throws out the whole number of 10. So for example, let’s put in 34 dollars here and the mod of 34 is going to be four. And so it shows us what the remainder is, which is good, that’s what I wanna do. So, I’m going to say, equal-if, open parenthesis, the mod of A1, comma, 10 is less than or equal to 0.99, comma.
Okay now, this is the part of true. Well at true, we want to round down to the last 10. And actually, it’s not a matter of rounding on to the last 10, it’s just a matter of taking off the decimals because we always know that it’s going to be within a dollar of the last 10. So, I’m going to use the int function, you also might use the trunc, T-R-U-N-C function, either one will work.
And then finally, if it is greater than 99 cents, then we want to round up. So now, we go back to the function I talked about it in the other podcast and number of digits. Well, zero would round to a dollar, one digit would round to the nearest 10 cents, two digits would round to the nearest penny so negative one digit will actually round to the nearest 10. Two closing parenthesis to finish that off and copy it down and you see the 21.5 rounds up to 30, that 29.5 rounds up to 30, 34 though rounds down to 40.
So, there you have it. Thanks for stopping by, we’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services