# Need formula to calculate comission & bonuses

S

#### Steve

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!!!

C

#### cht13er

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