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

[Jenny]

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?

[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 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>=100000,A1<=149999),0.80*A1,""))

[JE McGimpsey]

one way:

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


In article <[email protected]>,
"Jenny" <[email protected]> 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?

[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
[email protected]
"Jenny" <[email protected]> wrote in message
news:[email protected]...
> 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]

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 <[email protected]>,
"Don Guillett" <[email protected]> wrote:

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