Forum Discussion
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 🙂
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 ) ) )
2 Replies
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_Copper ContributorPRECISELY what I was after - and also managed to clean up some similar formulas.
Utterly grateful for your support!