Payout Percentage Based on Achievement Percentage
I have a table with achievement % minimum, target, and a max cap with corresponding payout percentage amounts. I need a formula that will look up any given % achieved and return the corresponding payout percentage, BUT the payout percentage could fall anywhere between the rows. In other words, these are NOT inflexible tiers. The payout percentages could be any decimal between 2% and 3.75%.
% Goal Achieved
Payout Percentage
Min
80.000%
2.000%
Target
100.000%
2.500%
Max
150.000%
3.750%
Expected Results:
>If employee achieved 79% of goal, result would be 0% payout based on minimum not achieved.
>If employee achieved 90% of goal, result would be 2.25% payout (i.e. between the 2% and 2.5% tier)
>If employee achieved 175% of goal, result would be capped at the 3.75% payout.
Assume the % goal value I am looking up is in cell A1, what formula can I enter in B1 to return the corresponding payout percentage?
I have a table with achievement % minimum, target, and a max cap with corresponding payout percentage amounts. I need a formula that will look up any given % achieved and return the corresponding payout percentage, BUT the payout percentage could fall anywhere between the rows. In other words, these are NOT inflexible tiers. The payout percentages could be any decimal between 2% and 3.75%. % Goal AchievedPayout PercentageMin80.000%2.000%Target100.000%2.500%Max150.000%3.750%Expected Results:>If employee achieved 79% of goal, result would be 0% payout based on minimum not achieved.>If employee achieved 90% of goal, result would be 2.25% payout (i.e. between the 2% and 2.5% tier)>If employee achieved 175% of goal, result would be capped at the 3.75% payout.Assume the % goal value I am looking up is in cell A1, what formula can I enter in B1 to return the corresponding payout percentage? Read More
