Value interpolation trough multiple ranges

[EngineerBM]

Hi,

I'm struggling with a problem here, with processing some data in excel - I'm out of ideas,please help.

so, I have a large amount of data that looks like this:



So to explain, i have a lot of cycles of measurements, (Cycle 1, Cycle2 in example picture), and each cycle gives a "y" values for random "x" values within a range. What I need, is to get a "y" value of a fixed "x" (lets say x=50), for each cycle. Its obvious in the example picture, it would be y=500 for both cycles.

In my case, I expect the value to be slightly different for each cycle. There is too much cycles to do the interpolation manually, and also, cycles are not of equal length (sometimes there is 8 measurements in the cycle, sometimes 10,11....). I can only differentiate the cycles by identifier index.

Any ideas on a function, formula... that would do the trick? I'm familiar also with VBA Excel Macros, if that helps.

Kind Regards

 

[AlanY]

give this a try

Excel 2013/2016
	A	B	C	D	E	F	G
1	identifier	x	y		identifier	x	y
2	1	13	130		1	85	850
3	1	26	260		2	85	1700
4	1	39	390
5	1	56	560
6	1	79	790
7	1	97	970
8
9	2	11	220
10	2	23	460
11	2	37	740
12	2	53	1060
13	2	75	1500
14	2	83	1660
15	2	93	1860
Sheet2

Cell Formulas
Range	Formula
G2	{=INDEX($C$2:$C$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15)))+(INDEX($C$2:$C$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))+1)-INDEX($C$2:$C$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))))*(F2-INDEX($B$2:$B$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))))/(INDEX($B$2:$B$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))+1)-INDEX($B$2:$B$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlanY]

the same formula before all merged into one

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	identifier	x	y		identifier	x					y
2	1	13	130		1	85	790	970	79	97	850
3	1	26	260		2	85	1660	1860	83	93	1700
4	1	39	390
5	1	56	560
6	1	79	790
7	1	97	970
8
9	2	11	220
10	2	23	460
11	2	37	740
12	2	53	1060
13	2	75	1500
14	2	83	1660
15	2	93	1860
Sheet2

Cell Formulas
Range	Formula
K2	=G2+(H2-G2)*(F2-I2)/(J2-I2)
G2	{=INDEX($C$2:$C$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15)))}
H2	{=INDEX($C$2:$C$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))+1)}
I2	{=INDEX($B$2:$B$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15)))}
J2	{=INDEX($B$2:$B$15,MATCH(F2,IF($A$2:$A$15=E2,$B$2:$B$15))+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlanY]

slightly more robust

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	identifier	x	y		identifier	x					y
2	1	13	130		1	97	970	220	97	11	970
3	1	26	260		2	22	220	460	11	23	440
4	1	39	390
5	1	56	560
6	1	79	790
7	1	97	970
8	2	11	220
9	2	23	460
10	2	37	740
11	2	53	1060
12	2	75	1500
13	2	83	1660
14	2	93	1860
Sheet2

Cell Formulas
Range	Formula
G2	{=INDEX($C$2:$C$14,MATCH(F2,IF($A$2:$A$14=E2,$B$2:$B$14)))}
H2	{=INDEX($C$2:$C$14,MATCH(F2,IF($A$2:$A$14=E2,$B$2:$B$14))+1)}
I2	{=INDEX($B$2:$B$14,MATCH(F2,IF($A$2:$A$14=E2,$B$2:$B$14)))}
J2	{=INDEX($B$2:$B$14,MATCH(F2,IF($A$2:$A$14=E2,$B$2:$B$14))+1)}
K2	{=IFERROR(INDEX($C$2:$C$14,MATCH(F2,IF($A$2:$A$14=E2,$B$2:$B$14,0),0)),G2+(H2-G2)*(F2-I2)/(J2-I2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[EngineerBM]

Thanks a milion, Alan! You are amazing. Works great!

 

[AlanY]

you're welcome

 

[EngineerBM]

Hey guys, seeking help once again
posting here bcs is basically the same problem, with a little twist.

Tried the same solution, but excel is acting a bit weird. Works on some rows, not on others. Difference in the new set of data is that input values in one cycle first decrease, and then increase again. I'm guessing that that is what's causing the problem because if I just sort the input data, formulas work. But that's not really the solution. I need the data to stay sorted like this... because "y" value for the same "x", is not the same in the decreasing part of the cycle, as is in increasing part. My goal is to get the interpolated value for the fixed "x" (7.7) in the second part of the cycle, when values are increasing, and to ignore the decreasing part.

Question is, can somebody think of a way to modify the formula, so that it ignores the part of the cycles with descending values?
Or some other workaround?
Was trying with IF functions... but keep getting #N/A errors.

Here a screenshot of my actual data:

 

[AlanY]

this works with a helper column D that can be hidden if needed,

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	identifier	x	y	helper	identifier	x					y
2	1	13	130	13	1	97	970	220	97	11	970.0
3	1	26	260	26	2	22	220	460	11	23	440.0
4	1	39	390	39	3	22	220	460	11	23	440.0
5	1	56	560	56
6	1	79	790	79
7	1	97	970	97
8	2	11	220	11
9	2	23	460	23
10	2	37	740	37
11	2	53	1060	53
12	2	75	1500	75
13	2	83	1660	83
14	2	93	1860	93
15	3	25	220	0
16	3	20	460	0
17	3	7	740	0
18	3	1	1060	1
19	3	11	220	11
20	3	23	460	23
21	3	37	740	37
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2=A3,IF(B2),IF(B2>B1,B2,0))
G2	{=INDEX($C$2:$C$21,MATCH(F2,IF($A$2:$A$21=E2,$D$2:$D$21)))}
H2	{=INDEX($C$2:$C$21,MATCH(F2,IF($A$2:$A$21=E2,$D$2:$D$21))+1)}
I2	{=INDEX($D$2:$D$21,MATCH(F2,IF($A$2:$A$21=E2,$D$2:$D$21)))}
J2	{=INDEX($D$2:$D$21,MATCH(F2,IF($A$2:$A$21=E2,$D$2:$D$21))+1)}
K2	{=IFERROR(INDEX($C$2:$C$21,MATCH(F2,IF($A$2:$A$21=E2,$D$2:$D$21,0),0)),G2+(H2-G2)*(F2-I2)/(J2-I2))}
Press CTRL+SHIFT+ENTER to enter array formulas.