Mar 19 2019 05:17 PM
I can't get less than and more than to work how I'd like it to in an IFS function.
=IFS((RIGHT(ROUNDUP(SUM(((G6*0.55)+G6)+((G6*0.55)+G6)/11),2),4))<=5,CEILING(SUM(((G6*0.55)+G6)+((G6*0.55)+G6)/11),5),RIGHT(ROUNDUP(SUM(((G6*0.55)+G6)+((G6*0.55)+G6)/11),2),4)>5,CEILING(SUM(((G6*0.55)+G6)+((G6*0.55)+G6)/11),10)-1)
What I'm trying to achieve: numbers below 5 round to a 5, numbers above 5 round to a 9.
RIGHT(ROUNDUP(SUM)) = $4.46 in the first cell I'm working on, but <=5 at the end of the first logical test comes up false. It seems to be happening with all numbers below 5, so everything is being rounded up to 9.
Mar 19 2019 07:20 PM
nevermind, put SUM in front of each RIGHT function, and now it works :)
Mar 21 2019 01:43 PM
@UserID303932 , if you'd like to have only 5 or 9 you may use
=(G6>5)*4+5
Mar 21 2019 04:31 PM
Mar 22 2019 08:29 AM
.. or if you want to name the coins
= CEILING(amount, nickel) - (MOD(amount, dime)>nickel)*penny
Interestingly an exact number of dollars will go down 1 cent to the 99 cents below. This is due to rounding error caused by the fact that decimal coinage does not have an exact representation in binary arithmetic.