Nested if with threshold and cap

New Contributor

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 - which is why it is important to use the ranges I address below. This is also what makes me failing in a solution. Unfortunately it is not a sliding scale but two gradients which also makes it a bit more difficult.


So what I need is a formula that captures below: 

  •  If outcome is between threshold-Mid (2-4) the bonus outcome should be 50% at 2, with a sliding scale to 100% at 4.
  • If outcome is between mid-cap(4-5) the bonus outcome should be 100% at 4, with a sliding scale to 150% at 5.
  • Note that the gradients between threshold-mid and mid-cap which is why i split it above
  • If below 2 then outcome should be 0, and if above 5 it should be capped at 150%.


% achievement50%100%150%
Increase target245


I tried myself to do it - but only succeeded without my threshold and cap by following formulas that were thereafter added together:




In other words, above is not what is needed

2 Replies
best response confirmed by S_N_L_A_ (New Contributor)


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 ) ) )
PRECISELY what I was after - and also managed to clean up some similar formulas.
Utterly grateful for your support!