# Linear Interpolation

1. ## Linear Interpolation

Hello everyone,

I have two columns of data: "X" values in A1 thru A200 and their corresponding "Y" values in B1 thru B200.
The numbers in A column starts from -1.4, 0.4 , 1.4 .... up to around 200 (none of those numbers are whole numbers)

In C1 thru C200, I write whole numbers starting with 0 thru 200. These will be the new "X" values.
In the D column I need the corresponding, linear interpolated "Y" values" for each whole number "X" values.

How can I write interpolation in the D Column? (the intervals between each "X" values are not always uniformly spaced)

Thank you  Register To Reply

2. ## Re: Linear Interpolation

Have you tried linest() function to derive a line of best fit?  Register To Reply

3. ## Re: Linear Interpolation

Are you required to use Excel? This is perfectly straightforward using Gnumeric's INTERPOLATION() function D1 is simply =INTERPOLATION(\$B\$1:\$B\$200,\$A\$1:\$A\$200,C1) copied down.

If you must use Excel, I usually recommend something like this: https://www.excelforum.com/excel-for...ml#post5262186 where you use two additional helper columns to compute slope and intercept for each segment, then use a lookup function (like VLOOKUP()) to extract the slope and intercept at your new x value.  Register To Reply

4. ## Re: Linear Interpolation

I could not quite get it to work. Maybe I didn't write question correctly.
I saw that we could upload a sample worksheet.
Could you take a look the sample sheet.
Basically I need the E-column to give me interpolated "reading" based on the whole number depth in D-column.
And the intervals of the depths in column A is not uniform.

Thank you  Register To Reply

5. ## Re: Linear Interpolation

I did not see any attempt at entering SLOPE() or INTERCEPT() or VLOOKUP() or anything, so I'm not sure what did not work for you. What I expected was something like:

0) Insert 3 columns between the input table and the output table to make room for the slope and intercept columns (so the output depth column is now column G).
1) In C2, enter =SLOPE(B2:B3,A2:A3). Copy/paste/fill down to C19.
2) In D2, enter =INTERCEPT(B2:B3,A2:A3). Copy/paste/fill down to D19.
3) In H2 (the interpolated reading column) enter =VLOOKUP(G2,\$A\$2:\$D\$19,4,TRUE)+VLOOKUP(G2,\$A\$2:\$D\$19,3,TRUE)*G2. Copy/paste/fill down to H22.

Which of those steps are you having trouble with?  Register To Reply