Another question from Gopal. New India Pay Commission guidelines suggests that pay should be rounded to increments of 10. If the amount over the last 10 is less than or equal to 0.99, then round down. Otherwise round up. While Excel’s built-in functions don’t handle this, Episode 1042 shows you how to handle this using three functions in Excel.
This is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new video every workday to learn one of 377 tips from the book!
This is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new video every workday to learn one of 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.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, we have to visit this rounding question again.
The question send 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 ten dollars up to and including the decimal 0.99 should be rounded down at the last ten dollars.
But, once you get above that, once you get to you know like for example, $21.
It should be rounded up to the next ten.
And so, you know none of Excel's building rounding functions are going to deal with that.
I'm going to break it down.
I might use a cool function here called MOD.
Now, I know that MOD, takes a number and divide it by divisor and then reports the remainder.
So, for example, 24 MOD 20 will report 4.
I didn't know what was going to 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 it in 34 dollars here and the MOD of 34 is going to be 4.
And so, it shows us what the remainder is which is good.
That's what I want to do.
So, I'm going to say, =IF(MOD(A1,10)<=0.99, Okay now, this is the part of true.
Well, if true, we want to round down to the last 10.
and actually it's not a matter of rounding down to the last ten.
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 ten.
So, I'm going to use the INT function.
You also might use the TRUNC, TRUNC function either one will work, and then finally if it is greater than ninety-nine cents, then we want to round up.
So, now we go back to the function I talked about in the other podcast and number of digits.
Well, 0 would round to a dollar, one digit would round to the nearest ten cents, two digits would round to the nearest penny.
So,negative 1 digits 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, 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 MrExcel.
Thanks for stopping by.
We'll see you next time for another from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, we have to visit this rounding question again.
The question send 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 ten dollars up to and including the decimal 0.99 should be rounded down at the last ten dollars.
But, once you get above that, once you get to you know like for example, $21.
It should be rounded up to the next ten.
And so, you know none of Excel's building rounding functions are going to deal with that.
I'm going to break it down.
I might use a cool function here called MOD.
Now, I know that MOD, takes a number and divide it by divisor and then reports the remainder.
So, for example, 24 MOD 20 will report 4.
I didn't know what was going to 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 it in 34 dollars here and the MOD of 34 is going to be 4.
And so, it shows us what the remainder is which is good.
That's what I want to do.
So, I'm going to say, =IF(MOD(A1,10)<=0.99, Okay now, this is the part of true.
Well, if true, we want to round down to the last 10.
and actually it's not a matter of rounding down to the last ten.
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 ten.
So, I'm going to use the INT function.
You also might use the TRUNC, TRUNC function either one will work, and then finally if it is greater than ninety-nine cents, then we want to round up.
So, now we go back to the function I talked about in the other podcast and number of digits.
Well, 0 would round to a dollar, one digit would round to the nearest ten cents, two digits would round to the nearest penny.
So,negative 1 digits 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, 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 MrExcel.
Thanks for stopping by.
We'll see you next time for another from MrExcel.
