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%

 

[Joe4]

Welcome to the Board!

Can you provide a bunch of sample input values are your export outputs?

 

[destin0906]

So, that's the tricky part, I don't have every iteration of the possible output values, that's what the formula needs to figure out. So, if the group's target is 100%, and my personal result is 96.1%, then I need to see what my output value percent would be based on the chart (think of this an an example of a bonus payout). So, my 96.1% would fall somewhere between the min and target (90%-100%), which means my output value or "payout score" would be some value between 25% and 100%. If my score were were 105%, then my score would fall somewhere between 100%-150%.

Does this help?

 

[saboh12617]

hello,

I propose you a solution based on the LINEST function to retrive the growth coefficients.
It is very "didactic", there are certainly smaller formulas.

So with your table in A1:D2, and the looked value in B4,

=LET(tbl, $A$1:$D$2,
val, B4,
i_1, XMATCH(val, TAKE(tbl, 1), -1), i_2, XMATCH(val, TAKE(tbl, 1), 1),
xs, INDEX(TAKE(tbl, 1), VSTACK(i_1, i_2)),
ys, INDEX(TAKE(tbl, -1), VSTACK(i_1, i_2)),
val*INDEX(LINEST(ys, xs), 1)+INDEX(LINEST(ys, xs), 2))

 

[Joe4]

I am not expecting every possible iteration, I just want to see a handful of values representing different scenarios to ensure we have a complete understanding of what you expect this to do.
Please show us a handful of examples.

 

[saboh12617]

A little twist to make it shorter:

=LET(
tbl, $A$1:$D$2,
val, B4,
i, XMATCH(val, TAKE(tbl, 1), VSTACK(-1, 1)),
xs, INDEX(TAKE(tbl, 1), i),
ys, INDEX(TAKE(tbl, -1), i),
val * INDEX(LINEST(ys, xs), 1) + INDEX(LINEST(ys, xs), 2))

However yes, sample data would be useful to verify the correctness.

 

[destin0906]

I am not expecting every possible iteration, I just want to see a handful of values representing different scenarios to ensure we have a complete understanding of what you expect this to do.
Please show us a handful of examples.

Understood - does this help? Goal is to figure out a formula for the Adj. Weight col; that would feed into the score col. Here it seems silly probably, but the weight won't always be 100 if there are other contributing line items. Tried to keep it high level to figure out for a simple example.

Employees	Actual	Percent to Target	Target	Adjusted Weight	Full Weight	Payout Score
Mickey Mouse	0.96	0.96	1	XXXX	100	Adj x Full
Minnie Mouse	1.05	1.05	1	XXXX	100	Adj x Full
Donald Duck	0.90	0.90	1	XXXX	100	Adj x Full
Daisy Duck	0.85	0.85	1	XXXX	100	Adj x Full
Pluto	0.93	0.93	1	XXXX	100	Adj x Full

 

[Joe4]

Did you try the formulas that saboh12617 gave you?

 

[destin0906]

Did you try the formulas that saboh12617 gave you?

I've not used the functions before so trying to play around with it, but this is more advanced that my current skillset.

 

[destin0906]

Thank you @saboh12617! I was able to figure out the formula provided, and it works! Really appreciate the help!