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

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?

one way:

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


In article ,
"Jenny" wrote:

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?

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

"Jenny" wrote in message
...
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?

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)


In article ,
"Don Guillett" wrote:

=LOOKUP(SUM(A7:A10),{0,75000,100000,150000},{0,0.3 ,0.8})*sum(a7:a10)

Jenny Wrote:
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?

Try this formula ...

=if(and(A1=75000,A1<=99999),0.30*A1,if(and(A1=10 0000,A1<=149999),0.80*A1,""))


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=346866