Forum Discussion
Payout Percentage Based on Achievement 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.
- 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 )
