Round to the closest nickel

Discuss the spreadsheet application

Round to the closest nickel

Postby RusselB » 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
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.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5303
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Round to the closest nickel

Postby Villeroy » Wed May 08, 2019 7:50 am

=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.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests