vb to extrapolate data

[men5j2s]

I have 2 columns of data in a workbook and need to use VBA to extrapolate data.

I need to know Y for any value of X.

I have a range of known Ys and Xs (as below)

The trickier part here might be that I only want to consider part of the range to extrapolate (say the middle 6 values) - I then to be able to determine the difference between the extrapolated values and the ignored values. (compare Y at x=9 in the table vs extrapolated Y)

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3.2[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3.7[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]3.8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]4.2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]8.5[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3.2[/TD]
[/TR]
[TR]
[TD]9.5[/TD]
[TD]2.8[/TD]
[/TR]
</tbody>[/TABLE]

If someone could get me going on this it would be amazing.. I'm really struggling.

The practical reason I'm doing this is to be able to determine the position at which a straight line curves off on a graph.

 

[Rick Rothstein]

Assuming your table of values starts in cells A2:B2 (cells A1:B1 are assumed to contain the X, Y header text) and the value you want to interpolate is in cell C1, then give this formula a try...

=(C1-VLOOKUP(C1,A2:B11,1,TRUE))*(VLOOKUP(C1+0.5,A2:B11,2,TRUE)-VLOOKUP(C1,A2:B11,2,TRUE))/(VLOOKUP(C1+0.5,A2:B11,1,TRUE)-VLOOKUP(C1,A2:B11,1,TRUE))+VLOOKUP(C1,A2:B11,2,TRUE)