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)

X	Y
5	3.2
5.5	3.5
6	3.7
6.5	3.8
7	4
7.5	4.2
8	4.3
8.5	4.5
9	3.2
9.5	2.8

<tbody>
</tbody>

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)