Help with a range formula

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello,

In my spreadsheet I have a percent listed in C20 (206%), this is the
salespersons percent to quota. Below I have a chart that breaks down the
salespersons payout based on that percent

Sales Bonus Schedule:
Percent to Quota Bonus
N1 10-19% N2 $125
O1 20-39% O2 $500
P1 40-99% P2 $1,500
Q1 100-199% Q2 $2,000
R1 200-400% R2 $5,000
S1 401% and above S2 $10,000

In D20 I would like to return the amount of the bonus based on the percent
to quota. Something to keep in mind is that the cells that show the percent
to quota range in N1-S1 are in the same cell. This is hte part im having
difficulty with from a formula percepective.

Thanks for the help.
Jim
 
Your schedule is set up incorrectly and you will not have luck with that. I
created this table in A2:B7

10% 125
20% 500
40% 1,500
100% 2,000
200% 5,000
401% 10,000

and used this formula
=INDEX(B2:B7, MATCH(C20, A2:A7,1))
 
Jim,

your solution is perfect. I don't know I would have thought about making
change. I have one follow up. Adding one additional criteria: How would you
write the formula for the following, where percent to quota is listed in C20
and the reps title is listed in B20? The headers for the payout amounts
match B20, but there are three possibilities.

Percent to Quota AE SAE MAE
0 $ - $ - $ -
20 $350 $500 $750
50 $700 $1,000 $1,500
70 $1,050 $1,500 $2,250
80 $1,750 $2,500 $3,750
90 $2,450 $3,500 $5,250
100 $3,500 $5,000 $7,500
125 $4,375 $6,250 $9,375
150 $5,250 $7,500 $11,250
200 $7,000 $10,000 $15,000
250 $8,750 $12,500 $18,750
350 $12,250 $17,500 $26,250
500 $15,750 $22,500 $33,750
 
similar to this... with your table in A1:D14

=INDEX(B2:D14, MATCH(C20, A2:A14, 1), MATCH(B20, B1:D1, 0))
 
Back
Top