## Round to the closest nickel

### Round to the closest nickel

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
Code: Select all   Expand viewCollapse view
`=value(right(A1;1))`
where A1 contains the starting amount.
A simple (though compound) IF can return the 5 or 0 amounts.
Code: Select all   Expand viewCollapse view
`=if(B1<2;-;if(B1<8;5;0))`

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5888
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Round to the closest nickel

=MROUND(A1;0.05)
or
=ROUND(A1*20)/20
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 27872
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany