Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time problem, Auto calculation??? | Excel Discussion (Misc queries) | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |