Nested if with threshold and cap

[S_N_L_A_]

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 · Accepted solution]

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

[#3]

PRECISELY what I was after - and also managed to clean up some similar formulas.
Utterly grateful for your support!