Formula Help

  • Thread starter Thread starter Kelly Frank
  • Start date Start date
K

Kelly Frank

I am trying to set a formula with 3 parts for a
commissions worksheet. Based on the percentage of budget
the salesperson reaches, their pay percentage is different.
FOr example, if the hit 100%+ of the budget, the get paid
20% of billing; if they hit 90%-99%, they get 18%; and if
they hit less then 89%, they get 16%.

ANy ideas how to set this up so when I enter the billing
and budget totals, it will automatically calculate which
percentage to pay?

Thanks!
 
Try this:

Set this "commissions" table up
in say Sheet1, A1:B3

0%....16%
90%...18%
100%..20%

Note:
------
The above assumes a revised "re-phrasing" of your criteria description as:
Percent Achieved < 90%, Commission Percent: 16%
Percent Achieved >=90%, <100%, Commission Percent: 18%
Percent Achieved >=100%, Commission Percent: 20%

Your criteria description was a little "loose" with some gaps undefined,
E.g.: 89% - 99% >> ??, 99% - 100% >> ??

In Sheet2,
------------
Assuming:
Budget* amounts in col A
Billing* amounts in col B
(data from row2 down)

Put in C2: =VLOOKUP(B2/A2,Sheet1!$A$1:$B$3,2,TRUE)

Format C2 as percentage

Copy C2 down the col

Col C will give the commission percentages
for the budget & billing amounts in cols A & B

*My understanding of your post is:
"Budget" = Sales Targets (eg: in dollars)
"Billings" = Sales (eg: in dollars)
 
Back
Top