A formula that is: if the sum is this, then muliply by this?

[Guest]

I want a formula that will calculate like this:

If the total in this cell is between 75,000 and 99,999 then muliply it by
..30. If it's between 100,000 and 149,999 then muliply it by .80. Is this
possible?

 

[JE McGimpsey]

one way:

=IF(OR(A1<75000,A1>=150000),"out of range",IF(A1<100000,0.3,0.8)*A1)

 

[Don Guillett]

This lookup formula will do it.
=LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3,0.8})

=LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3,0.8})*sum(a7:a10)
Don Guillett
SalesAid Software
(e-mail address removed)

 

[JE McGimpsey]

Note that this gives #N/A for values >150000. IF it's desired that those
values return 0, then you could use

=LOOKUP(SUM(A7:A10),{0,75000,100000,150000,1E+307},{0,0.3,0.8,0}) *
SUM(A7:A10)

 

[BenjieLop]

I want a formula that will calculate like this:

If the total in this cell is between 75,000 and 99,999 then muliply i
by
..30. If it's between 100,000 and 149,999 then muliply it by .80. I
this
possible?

Try this formula ...

=if(and(A1>=75000,A1<=99999),0.30*A1,if(and(A1>=100000,A1<=149999),0.80*A1,"")