Interpolate missing data

[roberco30]

Hi,
I'm looking for a VB script for interpolating missing data. Here is an example. I would like to interpolate the values in cells Y2 and Y3 using X1, X4, Y1 and Y4 values. Similarly, I would like to interpolate the values in cells Y6/7/8 using the values X5, X9, Y5, and Y9. Thanks!
X Y
0 1
1
2
3 4
4 5
5
6
7
8 9

[Crooza]

Condense your data to just know y and known x values then use the linest function.

If you post your worksheet someone (or I) will show you how.

[roberco30]

Please see the attached file for example. I would like to interpolate across the active range B2:D12 as Yvalues using A1:A12 as Xvalues.
<w w w . excelforum.com/attachment.php?attachmentid=693448&stc=1&d=1598972839>

[Crooza]

See the attached.

I took your first data set of x and y data only and condensed in in cells A14 to B19 to show just the pairs where you had full data.

I then used the linest formula to determine determine the parameters of a line of best fit. Aline can be described in the form as y = mx + b. where m is the gradient of th eline and b in the y axis intercept. If you use the charting tool and add a trend line and add the trendline formula you get the same form of equation shown on the chart too. I did this to show you that you get the same results as well.

The linest formula returns two answers (the gradient, m, and the intercept b) to get two answers you need to set the formula as an array formula to spread those two answers over two cells. the way to do this is type =linest(b15:b19, a15,a19,false) into cell d15, then highlight cell d15 AND E15 then press control + shift + enter together. this puts the formula across the two cells an gives the two parameters for the line function.

Now all you need to do to interpolate is to set up a column of y calculated data in column F and use the formula =A2*$D$15+$E$15 to apply the gradient and intercept to each x value and drag down.

In this particular example your data doesn't represent much of a line so the line of best fit is a bit ordinary.

I trust this is what you're after. If in stead you want to interpolate linearly between each individual data point then that's a different matter.

[roberco30]

Thank you! I was looking for a VB script, macro, or way to interpolate data between two points, using the data in the first column as X values.

[MrShorty]

Are you required to use Excel? Gnumeric with its built in INTERPOLATION() function makes short work of this. With Crooza's helper table in A15:B19, F2 becomes a simple =INTERPOLATION($A$15:$A$19,$B$15:$B$19,A2,0) copied/filled down to F12.

If you must use Excel, you will need to build your own interpolation algorithm. I like an approach like this, that adds a couple more helper columns to Crooza's helper table, then performs some simple lookups along the way: https://www.excelforum.com/excel-for...ml#post5262186