Forum Discussion

jj316's avatar
jj316
Copper Contributor
Sep 26, 2024
Solved

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 pay...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Sep 26, 2024

    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.

     

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 26, 2024

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.

 

 

Resources