Forum Discussion

S_N_L_A_'s avatar
S_N_L_A_
Copper Contributor
May 23, 2022
Solved

Nested if with threshold and cap

Hi all!   New to this site after a week of pulling my hair.   I have a case where I've been tasked to calculate an outcome of bonus levels. The % outcome of below is used in another formula - whi...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 23, 2022

    S_N_L_A_ 

    It could be

    =IF(outcome>=cap, capP,
  IF(outcome>mid, capP + (capP - midP)/(cap-mid)*(outcome-cap),
  IF(outcome>=thresh., threshP + (midP-threshP)/(mid-thresh.)*(outcome-thresh.), 0 ) ) )
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 23, 2022

S_N_L_A_ 

It could be

=IF(outcome>=cap, capP,
  IF(outcome>mid, capP + (capP - midP)/(cap-mid)*(outcome-cap),
  IF(outcome>=thresh., threshP + (midP-threshP)/(mid-thresh.)*(outcome-thresh.), 0 ) ) )
  • S_N_L_A_'s avatar
    S_N_L_A_
    Copper Contributor
    May 23, 2022
    PRECISELY what I was after - and also managed to clean up some similar formulas.
    Utterly grateful for your support!

Resources