Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
I am trying to set up an incentive matrix for sales reps. If there were only
3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. 
#2
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
Assuming your revenue table is begins at cell A1 you would have to add a row
for Teir 0 such as: % of Goal Achieved % of Revenue Share Teir 0 89% 0 Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the €śrange lookup€ť part of the function is set to €śTrue€ť anything 89% or less will return Zero. Any value 90% to 125% will return 75%. Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. 
#3
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
My bad... Just reread my post. Set Cell b2 to 0. Then it will return any
percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: % of Goal Achieved % of Revenue Share Teir 0 89% 0 Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the €śrange lookup€ť part of the function is set to €śTrue€ť anything 89% or less will return Zero. Any value 90% to 125% will return 75%. Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. 
#4
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
Thanks but actually that's not what I need. I know how to make it do that.
I don't want values between 90% and 125% to round down to 0.75 or up to 3.00; I want it to give me a number in between 0.75 and 3.00. For example, a % of goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue share. "FrankWood" wrote: My bad... Just reread my post. Set Cell b2 to 0. Then it will return any percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: % of Goal Achieved % of Revenue Share Teir 0 89% 0 Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the €śrange lookup€ť part of the function is set to €śTrue€ť anything 89% or less will return Zero. Any value 90% to 125% will return 75%. Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. 
#5
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
Assuming your table occupies A1:C4 like this:
%_of_Goal_Achieved %_of_Revenue_Share Tier_1 90.00% 0.75% Tier_2 125.00% 3.00% Tier_3 150.00% 4.50% and that the percentage widgets is in B10, then you can put this formula in C10: =IF(B10<B$2,0,IF(B10=B$4,C$4,INDEX(C$2:C$4,MATCH( B10,B$2:B$4))+(B10 INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))*(INDEX(C$2:C$4, MATCH(B10,B$2:B $4)+1)INDEX(C$2:C$4,MATCH(B10,B$2:B$4)))/(INDEX(B$2:B$4,MATCH(B10,B $2:B$4)+1)INDEX(B$2:B$4,MATCH(B10,B$2:B$4))))) to give you what you require. You can copy the formula down if you wish  here's some sample results: 80% 0.00% 85% 0.00% 90% 0.75% 95% 1.07% 100% 1.39% 105% 1.71% 110% 2.04% 115% 2.36% 120% 2.68% 125% 3.00% 130% 3.30% 135% 3.60% 140% 3.90% 145% 4.20% 150% 4.50% 155% 4.50% 175% 4.50% 200% 4.50% Hope this helps. Pete On Dec 10, 10:05*pm, jjones wrote: Thanks but actually that's not what I need. *I know how to make it do that. * I don't want values between 90% and 125% to round down to 0.75 or up to 3..00; I want it to give me a number in between 0.75 and 3.00. *For example, a % of goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue share. "FrankWood" wrote: My bad... *Just reread my post. *Set Cell b2 to 0. *Then it will return any percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: * * * % of Goal Achieved * * *% of Revenue Share Teir 0 * * * *89% * * 0 Tier 1 * * * *90.00% *0.75% Tier 2 * * * *125.00% 3.00% Tier 3 * * * *150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. * =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the “range lookup” part of the function is set to “True” anything 89% or less will return Zero. *Any value 90% to *125% will return 75%.. *Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. *If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. *What I actually need, however, is some way of calculating infinite ranges in between my established tiers. *So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. *My tiers are constructed like this: *% of Goal Achieved * * *% of Revenue Share Tier 1 * 90.00% * * * * * * * * * 0.75% Tier 2 * 125.00% * * * * * * * * *3.00% Tier 3 * 150.00% * * * * * * * * *4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. * If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). *But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). *And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. *And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. Hide quoted text   Show quoted text  
#6
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
Here's a shorter version of the formula, again looking at the
percentage of widgets in B10: =IF(B10<B$2,0,IF(B10=B$4,C$4,IF(B10<B$3,C$2+(B10B$2)*(C$3C$2)/(B$3B $2),C$3+(B10B$3)*(C$4C$3)/(B$4B$3)))) Hope this helps. Pete On Dec 11, 2:18*am, Pete_UK wrote: Assuming your table occupies A1:C4 like this: * * * * * * *%_of_Goal_Achieved * * %_of_Revenue_Share Tier_1 * * * * * * *90.00% * * * * * * * * * * * *0.75% Tier_2 * * * * * * 125.00% * * * * * * * * * * * 3.00% Tier_3 * * * * * * 150.00% * * * * * * * * * * * 4.50% and that the percentage widgets is in B10, then you can put this formula in C10: =IF(B10<B$2,0,IF(B10=B$4,C$4,INDEX(C$2:C$4,MATCH( B10,B$2:B$4))+(B10 INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))*(INDEX(C$2:C$4, MATCH(B10,B$2:B $4)+1)INDEX(C$2:C$4,MATCH(B10,B$2:B$4)))/(INDEX(B$2:B$4,MATCH(B10,B $2:B$4)+1)INDEX(B$2:B$4,MATCH(B10,B$2:B$4))))) to give you what you require. You can copy the formula down if you wish  here's some sample results: * 80% * * 0.00% * 85% * * 0.00% * 90% * * 0.75% * 95% * * 1.07% 100% * * 1.39% 105% * * 1.71% 110% * * 2.04% 115% * * 2.36% 120% * * 2.68% 125% * * 3.00% 130% * * 3.30% 135% * * 3.60% 140% * * 3.90% 145% * * 4.20% 150% * * 4.50% 155% * * 4.50% 175% * * 4.50% 200% * * 4.50% Hope this helps. Pete On Dec 10, 10:05*pm, jjones wrote: Thanks but actually that's not what I need. *I know how to make it do that. * I don't want values between 90% and 125% to round down to 0.75 or up to 3.00; I want it to give me a number in between 0.75 and 3.00. *For example, a % of goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue share. "FrankWood" wrote: My bad... *Just reread my post. *Set Cell b2 to 0. *Then it will return any percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: * * * % of Goal Achieved * * *% of Revenue Share Teir 0 * * * *89% * * 0 Tier 1 * * * *90.00% *0.75% Tier 2 * * * *125.00% 3.00% Tier 3 * * * *150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. * =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the “range lookup” part of the function is set to “True” anything 89% or less will return Zero. *Any value 90% to *125% will return 75%. *Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. *If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. *What I actually need, however, is some way of calculating infinite ranges in between my established tiers. *So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. *My tiers are constructed like this: *% of Goal Achieved * * *% of Revenue Share Tier 1 * 90.00% * * * * * * * * * 0.75% Tier 2 * 125.00% * * * * * * * * *3.00% Tier 3 * 150.00% * * * * * * * * *4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. * If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). *But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). *And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. *And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  
#7
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
Thanks, Pete! ;)
"Pete_UK" wrote: Here's a shorter version of the formula, again looking at the percentage of widgets in B10: =IF(B10<B$2,0,IF(B10=B$4,C$4,IF(B10<B$3,C$2+(B10B$2)*(C$3C$2)/(B$3B $2),C$3+(B10B$3)*(C$4C$3)/(B$4B$3)))) Hope this helps. Pete On Dec 11, 2:18 am, Pete_UK wrote: Assuming your table occupies A1:C4 like this: %_of_Goal_Achieved %_of_Revenue_Share Tier_1 90.00% 0.75% Tier_2 125.00% 3.00% Tier_3 150.00% 4.50% and that the percentage widgets is in B10, then you can put this formula in C10: =IF(B10<B$2,0,IF(B10=B$4,C$4,INDEX(C$2:C$4,MATCH( B10,B$2:B$4))+(B10 INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))*(INDEX(C$2:C$4, MATCH(B10,B$2:B $4)+1)INDEX(C$2:C$4,MATCH(B10,B$2:B$4)))/(INDEX(B$2:B$4,MATCH(B10,B $2:B$4)+1)INDEX(B$2:B$4,MATCH(B10,B$2:B$4))))) to give you what you require. You can copy the formula down if you wish  here's some sample results: 80% 0.00% 85% 0.00% 90% 0.75% 95% 1.07% 100% 1.39% 105% 1.71% 110% 2.04% 115% 2.36% 120% 2.68% 125% 3.00% 130% 3.30% 135% 3.60% 140% 3.90% 145% 4.20% 150% 4.50% 155% 4.50% 175% 4.50% 200% 4.50% Hope this helps. Pete On Dec 10, 10:05 pm, jjones wrote: Thanks but actually that's not what I need. I know how to make it do that. I don't want values between 90% and 125% to round down to 0.75 or up to 3.00; I want it to give me a number in between 0.75 and 3.00. For example, a % of goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue share. "FrankWood" wrote: My bad... Just reread my post. Set Cell b2 to 0. Then it will return any percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: % of Goal Achieved % of Revenue Share Teir 0 89% 0 Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the €śrange lookup€ť part of the function is set to €śTrue€ť anything 89% or less will return Zero. Any value 90% to 125% will return 75%. Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  . 
#8
Posted to microsoft.public.excel.worksheet.functions




3 tier incentive matrix w/ infinite possibilities
You're welcome.
Pete On Dec 11, 2:03*pm, jjones wrote: Thanks, Pete! ;) "Pete_UK" wrote: Here's a shorter version of the formula, again looking at the percentage of widgets in B10: =IF(B10<B$2,0,IF(B10=B$4,C$4,IF(B10<B$3,C$2+(B10B$2)*(C$3C$2)/(B$3B $2),C$3+(B10B$3)*(C$4C$3)/(B$4B$3)))) Hope this helps. Pete On Dec 11, 2:18 am, Pete_UK wrote: Assuming your table occupies A1:C4 like this: * * * * * * *%_of_Goal_Achieved * * %_of_Revenue_Share Tier_1 * * * * * * *90.00% * * * * * * * * * * * *0.75% Tier_2 * * * * * * 125.00% * * * * * * * * * * * 3.00% Tier_3 * * * * * * 150.00% * * * * * * * * * * * 4.50% and that the percentage widgets is in B10, then you can put this formula in C10: =IF(B10<B$2,0,IF(B10=B$4,C$4,INDEX(C$2:C$4,MATCH( B10,B$2:B$4))+(B10 INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))*(INDEX(C$2:C$4, MATCH(B10,B$2:B $4)+1)INDEX(C$2:C$4,MATCH(B10,B$2:B$4)))/(INDEX(B$2:B$4,MATCH(B10,B $2:B$4)+1)INDEX(B$2:B$4,MATCH(B10,B$2:B$4))))) to give you what you require. You can copy the formula down if you wish  here's some sample results: * 80% * * 0.00% * 85% * * 0.00% * 90% * * 0.75% * 95% * * 1.07% 100% * * 1.39% 105% * * 1.71% 110% * * 2.04% 115% * * 2.36% 120% * * 2.68% 125% * * 3.00% 130% * * 3.30% 135% * * 3.60% 140% * * 3.90% 145% * * 4.20% 150% * * 4.50% 155% * * 4.50% 175% * * 4.50% 200% * * 4.50% Hope this helps. Pete On Dec 10, 10:05 pm, jjones wrote: Thanks but actually that's not what I need. *I know how to make it do that. * I don't want values between 90% and 125% to round down to 0.75 or up to 3.00; I want it to give me a number in between 0.75 and 3.00. *For example, a % of goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue share. "FrankWood" wrote: My bad... *Just reread my post. *Set Cell b2 to 0. *Then it will return any percentage from 0  89% with % of revenue set to 0. "FrankWood" wrote: Assuming your revenue table is begins at cell A1 you would have to add a row for Teir 0 such as: * * * % of Goal Achieved * * *% of Revenue Share Teir 0 * * * *89% * * 0 Tier 1 * * * *90.00% *0.75% Tier 2 * * * *125.00% 3.00% Tier 3 * * * *150.00% 4.50% Then assuming the percentage of sales that you are looking up is in cell C19 you could use this formula. * =VLOOKUP(C18,$B$2:$C$5,2,TRUE) Since the “range lookup” part of the function is set to “True” anything 89% or less will return Zero. *Any value 90% to *125% will return 75%. *Anything 150% or above will return 4.5% Hope that helps. Frank "jjones" wrote: I am trying to set up an incentive matrix for sales reps. *If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. *What I actually need, however, is some way of calculating infinite ranges in between my established tiers. *So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each.. *My tiers are constructed like this: *% of Goal Achieved * * *% of Revenue Share Tier 1 * 90.00% * * * * * * * * * 0.75% Tier 2 * 125.00% * * * * * * * * *3.00% Tier 3 * 150.00% * * * * * * * * *4.50% If, for example, the rep sales only 89 widgets, he gets no revenue share. * If he sales 90 he gets 0.75% of the revenue (in this example that would be 0.75% of $90,000 or $675). *But if he hits something like 107.5% of his widget goal, then I want it to calculate a percentage of revenue share in between the first tier (0.75%) and the second tier (3.00%). *And then I need the same sort of calculation to take place if the numbers lie in between the 2nd and 3rd tiers. *And of course I need it capped at 150% of goal...so the most the rep could ever make is 4.50% of the revenue. Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  . Hide quoted text   Show quoted text  
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Calculation of incentive  Excel Worksheet Functions  
tier calculations  Excel Discussion (Misc queries)  
how to use tier system  Excel Worksheet Functions  
tier pricing  Excel Worksheet Functions  
Rebate incentive  Multiple tier payback  Confusing if Statement  Excel Discussion (Misc queries) 