Forum Discussion
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?
= IF(achieved<80%, 0, MEDIAN(achieved, 80%, 150%) / 40)The discontinuity at 80% requires the test; the linear interpolation and the cap are handled by the MEDIAN function. If you use 365 a slightly messy formula can be concealed within Lambda
= PayoutPercentλ(achieved) where PayoutPercentλ = LAMBDA(_achieved, IF(_achieved<80%, 0, MEDIAN(_achieved, 80%, 150%) / 40) )If the defined name 'achieved' applies to multiple values then the worksheet formula becomes
= MAP(achieved, PayoutPercentλ)in order to return a dynamic array of results. I suspect you will wish to simply undo the defined name 'achieved' and use A1, but you do have other options.
= IF(achieved<80%, 0, MEDIAN(achieved, 80%, 150%) / 40)The discontinuity at 80% requires the test; the linear interpolation and the cap are handled by the MEDIAN function. If you use 365 a slightly messy formula can be concealed within Lambda
= PayoutPercentλ(achieved) where PayoutPercentλ = LAMBDA(_achieved, IF(_achieved<80%, 0, MEDIAN(_achieved, 80%, 150%) / 40) )If the defined name 'achieved' applies to multiple values then the worksheet formula becomes
= MAP(achieved, PayoutPercentλ)in order to return a dynamic array of results. I suspect you will wish to simply undo the defined name 'achieved' and use A1, but you do have other options.
- That worked! Thanks, Peter!
PeterBartholomew1 alternatively:
= IF(achieved<80%, 0, MIN(achieved / 40, 3.75%) )or maybe
= (achieved>=80%)*MIN(achieved / 40, 3.75%) )Yes, the use of the "*" operator allows an array solution without resorting to Lambda. It may be somewhat faster as a result but I don't really know.
On the other hand, I am gradually moving to a programming style in which I wrap all formulas in LAMBDA and, at the same time develop larger blocks of functionality within each formula. Each named Lambda states the nature of the analysis performed and the parameter list provides a definitive statement of the data to be processed.
The discussion then becomes whether 'simplicity' demands that calculations are broken down into their smallest parts, using grade-school arithmetic where possible, or by providing clear statements of the nature of the calculation process at a more abstract level. Of course, this question addressed a specific sticking point, so the difference in strategy has more limited impact. There is also the matter of how embedded constants should be handled, but that is a matter that the OP didn't raise.
