User Profile
S_N_L_A_
Copper Contributor
Joined May 23, 2022
User Widgets
Recent Discussions
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 🙂Solved2.5KViews0likes2Comments
Recent Blog Articles
No content to show