Round to the closest nickel
Posted: Wed May 08, 2019 7:38 am
Being in Canada, when dealing with cash (not credit/debit cards or cheques), amounts now have to be rounded to the closest nickel, since pennies (officially) are not in circulation any longer.
I'm wondering if there's an easy way to do this using Calc.. I know, using Calc isn't the best for business purposes, but for this I think it'll work fine.
Basically if the amount is between .01 or .02, it gets rounded down to the 0, so $1.92 becomes $1.90
If it's .03 or .04, then it gets rounded up to the .05, so $1.93 becomes $1.95
Likewise with .06 & .07 rounding down, and finally .08 & .09 round up.
It's easy enough to determine the value of the last digit, using where A1 contains the starting amount.
A simple (though compound) IF can return the 5 or 0 amounts.
Where I've run into a stumbling block, is changing the last digit in A1 to match the value in C1, so that the amount in D1 is the cash amount correctly rounded per the instructions from Canada Revenue.
I'm wondering if there's an easy way to do this using Calc.. I know, using Calc isn't the best for business purposes, but for this I think it'll work fine.
Basically if the amount is between .01 or .02, it gets rounded down to the 0, so $1.92 becomes $1.90
If it's .03 or .04, then it gets rounded up to the .05, so $1.93 becomes $1.95
Likewise with .06 & .07 rounding down, and finally .08 & .09 round up.
It's easy enough to determine the value of the last digit, using
Code: Select all
=value(right(A1;1))A simple (though compound) IF can return the 5 or 0 amounts.
Code: Select all
=if(B1<2;-;if(B1<8;5;0))