IFS function + roundup, sum, and right

Copper Contributor

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.

4 Replies

nevermind, put SUM in front of each RIGHT function, and now it works :) 

@UserID303932 , if you'd like to have only 5 or 9 you may use

=(G6>5)*4+5

@UserID303932 

= CEILING(amount, 0.05) - (MOD(amount, 0.1)>0.05) * 0.01

 

@UserID303932 

.. 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.