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.

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.

My bad... Just re-read 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.

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 re-read 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.

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 re-read 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 -

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+(B10-B$2)*(C$3-C$2)/(B$3-B
$2),C$3+(B10-B$3)*(C$4-C$3)/(B$4-B$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 re-read 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 -

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+(B10-B$2)*(C$3-C$2)/(B$3-B
$2),C$3+(B10-B$3)*(C$4-C$3)/(B$4-B$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 re-read 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 -

.

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+(B10-B$2)*(C$3-C$2)/(B$3-B
$2),C$3+(B10-B$3)*(C$4-C$3)/(B$4-B$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 re-read 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 -