SOLVED

New Contributor

# 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 - 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%.

 Thresh. Mid Cap % achievement 50% 100% 150% Increase target 2 4 5

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

=IF(outcome<thresh.;0;IF(AND(Outcome>thresh.;Outcome<=Mid);((Outcome-thresh.)/(Mid-thresh.));1))

=IF(Outcome<=mid;0;IF(AND(outcome>mid;outcome<=cap);((outcome-mid)/(cap-mid));1))

In other words, above is not what is needed

2 Replies
best response confirmed by S_N_L_A_ (New Contributor)
Solution

# Re: Nested if with threshold and cap

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 ) ) )``````

# Re: Nested if with threshold and cap

PRECISELY what I was after - and also managed to clean up some similar formulas.