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 -