Value interpolation trough multiple ranges

EngineerBM

New Member
Joined
Apr 4, 2018
Messages
3
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

 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
give this a try


Excel 2013/2016
ABCDEFG
1identifierxyidentifierxy
2113130185850
31262602851700
4139390
5156560
6179790
7197970
8
9211220
10223460
11237740
122531060
132751500
142831660
152931860
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
the same formula before all merged into one


Excel 2013/2016
ABCDEFGHIJK
1identifierxyidentifierxy
21131301857909707997850
31262602851660186083931700
4139390
5156560
6179790
7197970
8
9211220
10223460
11237740
122531060
132751500
142831660
152931860
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
slightly more robust


Excel 2013/2016
ABCDEFGHIJK
1identifierxyidentifierxy
21131301979702209711970
31262602222204601123440
4139390
5156560
6179790
7197970
8211220
9223460
10237740
112531060
122751500
132831660
142931860
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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:

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


Excel 2013/2016
ABCDEFGHIJK
1identifierxyhelperidentifierxy
2113130131979702209711970.0
3126260262222204601123440.0
4139390393222204601123440.0
515656056
617979079
719797097
821122011
922346023
1023774037
11253106053
12275150075
13283166083
14293186093
153252200
163204600
17377400
183110601
1931122011
2032346023
2133774037
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top