Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to calculate comission & bonuses
I need to create a formula that will figure out comissions & bonuses based on
not only amount sold, but also based on how much of each product is sold. I have the breakdown as follows: Percentage to Goal Total Revenue Bonus Online Only Revenue Bonus 125% $4,000.00 $2,000.00 120% $3,600.00 $1,800.00 115% $3,200.00 $1,600.00 110% $2,800.00 $1,400.00 105% $2,400.00 $1,200.00 100% $2,000.00 $1,000.00 95% $1,600.00 $800.00 90% $1,200.00 $600.00 85% $800.00 $400.00 80% $400.00 $200.00 75% $80.00 $40.00 Below 75% $0.00 $0.00 Explanation of the bonus criteria: If a rep has a $20k goal and sells $20k (100%), they automatically earn the $2k bonus shown for Total Revenue. 20% of their goal needs to come from online only ads to earn the online bonus, too. So if this rep sells $4000 in online only sales from the $20k they sold they earn $1k also for a total of $3k. If a rep sells only $15k of their $20k bonus, but $4k of that amount sold is from online only then they earn $80 from the Total Revenue bonus AND $1k from the online bonus and so on. Now the question is, how would I enter that into the spreadsheet? I have tried using the formula and using IF & AND but I keep getting errors. Please help and THANK YOU!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to calculate comission & bonuses
On Mar 19, 5:26 pm, Steve wrote:
I need to create a formula that will figure out comissions & bonuses based on not only amount sold, but also based on how much of each product is sold. I have the breakdown as follows: Percentage to Goal Total Revenue Bonus Online Only Revenue Bonus 125% $4,000.00 $2,000.00 120% $3,600.00 $1,800.00 115% $3,200.00 $1,600.00 110% $2,800.00 $1,400.00 105% $2,400.00 $1,200.00 100% $2,000.00 $1,000.00 95% $1,600.00 $800.00 90% $1,200.00 $600.00 85% $800.00 $400.00 80% $400.00 $200.00 75% $80.00 $40.00 Below 75% $0.00 $0.00 Explanation of the bonus criteria: If a rep has a $20k goal and sells $20k (100%), they automatically earn the $2k bonus shown for Total Revenue. 20% of their goal needs to come from online only ads to earn the online bonus, too. So if this rep sells $4000 in online only sales from the $20k they sold they earn $1k also for a total of $3k. If a rep sells only $15k of their $20k bonus, but $4k of that amount sold is from online only then they earn $80 from the Total Revenue bonus AND $1k from the online bonus and so on. Now the question is, how would I enter that into the spreadsheet? I have tried using the formula and using IF & AND but I keep getting errors. Please help and THANK YOU!!! I set up this problem in a spreadsheet and found that a pair of vlookups did the job perfectly. I will try to send you the file - if you don't get it, could you email me so that I can send it to you? THanks Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating bonuses - but not a VLOOKUP or CEILING? | Excel Discussion (Misc queries) | |||
Sumproduct to find monthly bonuses | Excel Worksheet Functions | |||
need to work out bonuses | Excel Worksheet Functions | |||
comission split based on 365 days | Excel Worksheet Functions | |||
Calculate comission graduated scale, $2000=20%, $4000=40%, etc? | New Users to Excel |