Bonus Calculation problem

[JRinDallas]

The bonus amount will be 10% of any money collected over $20,000 up to
$40,000 and 15% of any money collected over $40,000.
Ex: $52,000 collected in one month would produce a bonus of $3800
(10%*20,000=$2,000)plus(15%*$2,000=$1800).

This formula calculates properly when the total money collect (E9) is
greater than 40K but returns 2000 for everything below that
=IF(OR(E9>20000,E9<=40000),((E9-(E9-20000))*0.1),0)

Changing the OR to AND produces the correct results for less than 40 but
zero for everything above that amount
=IF(AND(20000<E9,E9<=40000),((E9-20000)*0.1),0)

Any suggestions would be greatly appreciated. Thank you

 

[KL]

Hi JRinDallas

Try this:

=CHOOSE(1+(A1>20000)+(A1>40000),0,A1*0.1,(A1-40000)*0.15+4000)

Regards,
KL

 

[Paul987]

This will take care of it.

=IF(AND(A35>=20000,A35<=40000),(A35-20000)*0.1,IF(A35>=40000,(A35-40000)*0.15+2000,""))

where "A35" equals reference to money collected.

Since I will need help with a problem in a minute, I figured I would
take some time to answer someone elses. Hopefully someone will return
the favor!

 

[Dana DeLouis]

How about:

=MAX(0,(A1-20000)/10,(3*A1-80000)/20)

HTH :>)

 

[Dana DeLouis]

Well...probably better this way now that I think about it..

=MAX(0,A1*0.1-2000,A1*0.15-4000)

 

[Paul987]

I wish I saw this kind of effort put towards my problem!

 

[JRinDallas]

Thanks everyone for your assistance. Dana this one worked perfectly!

Thanks again

 

[srinivasan]

I wish I saw this kind of effort put towards my problem!!

A small correction in KL formula will also do I
think.=CHOOSE(1+(E9>20000)+(E9>40000),0,2000,(E9-40000)*0.15+2000)
This will give you zero for the sum below 20000 and Rs 2000 for the sum up
to 40000 and 15% of the value for the sum in excess over 40000 plus 2000.