Need formula for returning incremental number based on target ranges

[destin0906]

I've tried so many formulas and can't quite crack this. My cell should return the incremental percentage # if the value of the reference cell is within the range to determine a person's score with specified targets, using the the table below as inputs. So, for example, if the value of the ref. cell is 96.1%, I need to return the corresponding incremental percentage value between the 20% and 100%. If the value of the ref. cell is 105%, then need to return the incremental percentage value between 100% and 150%. Thoughts on what might work?

Below min	Min = 90%	Tgt = 100%	Max = 110%+
0	20%	100%	150%

 

[saboh12617]

Glad to help and thanks for the feedback!

To confirm your understanding, the formula works in this way:

1) It looks for the "equal or smaller" corresponding "x-value" in the top row (xmatch(…,-1)) and the "equal or larger" corresponding "y-value (xmatch(…,1)). XMATCH returns their indexes/positions in the top row.

2) Then we simply retrieve the corresponding x's (2 values) and y's (2 values) in the array, thanks to INDEX (we use an INDEX/MATCH).

3.1) Then we use LINEST to get the "a" and "b" coefficients, since we calculate the relation between x's and y's as y = a*x+b. And we get a and b with INDEX again.

3.2) Finally the corresponding y value to the given x is calculated with y = a*x+b with x := the input value.

 

[destin0906]

So helpful thanks!