Bonus Calculation problem
 

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(E920000,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

Hi JRinDallas

Try this:

=CHOOSE(1+(A120000)+(A140000),0,A1*0.1,(A1-40000)*0.15+4000)

Regards,
KL



"JRinDallas" wrote in message
...
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(E920000,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

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!


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387502

How about:

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

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"JRinDallas" wrote in message
...
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(E920000,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

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

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

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" wrote in message
...
How about:

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

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"JRinDallas" wrote in message
...
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(E920000,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

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


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387502

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

Thanks again

"Dana DeLouis" wrote:

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

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

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" wrote in message
...
How about:

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

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"JRinDallas" wrote in message
...
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(E920000,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

"Paul987" wrote:


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


--
Paul987



A small correction in KL formula will also do I
think.=CHOOSE(1+(E920000)+(E940000),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.



------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=387502