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.