Payout Percentage Based on Achievement Percentage

[jj316]

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?

[#2 · Accepted solution]

@jj316 

= 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.

[#3]

@PeterBartholomew1  alternatively:

= IF(achieved<80%, 0, MIN(achieved / 40, 3.75%) )

or maybe

= (achieved>=80%)*MIN(achieved / 40, 3.75%) )​

[#4]

That worked! Thanks, Peter!

[#5]

@m_tarler 

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.

[#6]

actually my alternatives were purely for the calculation itself and do not work for input arrays. I fully understand and commend your philosophy of embedding within a named Lambda and either of those options would fit nicely into your Lambda approach. That said, I do apply a pragmatic approach to names and Lambda and try to balance factors like simiplicity and usability.
If the goal was for a formula that could be used on an array of inputs then maybe something like:
= IFS(achieved<80%, 0, achieved>150%, 3.75%, 1, achieved / 40 )