Multiple IFS with Calculations

[slparadise]

I am having issues writing a formula that will change the discount of a quote based on a subtotal.

The Formula
=IFS(A188<35000,B189,B188<40000>35000.01,B190,A188<40000.01>45000,B191,B188<45000.01>50000,B192,A188<50000.01>55000,B193,A188<55000.01>60000,B194,A188<60000.01>70000,B195,A188<70000.01>80000,B196,A188<80000.01>100000,B197,A188<100000.01>150000,B198,A188>150000.01,B199)

The problem I encounter is that when the subtotal changes, the discount calculation does not.

I am open to any suggestions and would not be opposed to using a different formula if that would work better.

Thanks!!

 

[Special-K99]

My Excel doesnt have IFS but this doesnt look right.

"B188<40000>35000.01"

The < and > signs.
I'm sure you can't say that.

You'd have to use AND

AND(B188<40000,B188>35000.01)

This might explain why your formula doesn't return the correct value.

 

[Special-K99]

And this is definitely wrong.

B188<45000.01>50000

How can B188 be less than 45000.01 AND greater than 50000 at the same time ?

As a example if A6 is 13 this

=A6>0<15

produces FALSE

Same with these

A188<50000.01>55000
A188<55000.01>60000
A188<60000.01>70000
A188<70000.01>80000
A188<80000.01>100000
A188<100000.01>150000

and this last won't get excuted at all because of the if A188 >150000 above
A188>150000.01