| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a range formula
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a range formula
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)) -- HTH... Jim Thomlinson "Jim" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a range formula
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 "Jim Thomlinson" wrote: 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)) -- HTH... Jim Thomlinson "Jim" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a range formula
similar to this... with your table in A1:D14
=INDEX(B2:D14, MATCH(C20, A2:A14, 1), MATCH(B20, B1:D1, 0)) -- HTH... Jim Thomlinson "Jim" wrote: 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 "Jim Thomlinson" wrote: 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)) -- HTH... Jim Thomlinson "Jim" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with a range formula
Perfect again. Thank you!!!
"Jim Thomlinson" wrote: similar to this... with your table in A1:D14 =INDEX(B2:D14, MATCH(C20, A2:A14, 1), MATCH(B20, B1:D1, 0)) -- HTH... Jim Thomlinson "Jim" wrote: 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 "Jim Thomlinson" wrote: 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)) -- HTH... Jim Thomlinson "Jim" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| Range - Formula | Excel Worksheet Functions | |||
| conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| Sum Formula that excludes other sum formula in range | Excel Worksheet Functions | |||
Linear Mode
