Interpolation

[vic244]

Hi there,

Hoping somebody can hook with up with a quick and effective solution to the following problem.

I have a range of x-values from -180.0 to +180.0 in 0.1 increments. I have been given corresponding y-values for certain of these x-values.

What I'd like to do is use linear interpolation to fill in the gaps, small errors are not too important. I have been playing around with formulas and even writing some basic C code but I'm convinced that there has to be an easier way.

Does anybody know the easiest method for doing this?

I have included a sample of the task in the attachment,
Regards,
g

[MrShorty]

A couple of ideas:

1) Sometimes I think the easiest way to do linear interpolation is to get a different spreadsheet program (Quattro Pro for example) which has a built in linear interpolation function. You may have to extract from the existing table all the values where you have a known y into a new table and then build the complete table from that reduced table.

2) In Excel, the TREND function can be used, if you are willing to manually locate the endpoints of each interval over which you are interpolating. If this is a one time operation, this may be the easiest even if it's a bit tedious.

3) You don't seem too excited about programming this routine in C, so I don't know if this is better. For me, personally, one of the first things I did when I converted from QP to Excel was to write my own linearinterpolation user-defined-function (UDF) in VBA. This may be a good option if you find yourself doing a lot of interpolation and you are unable/unwilling to switch to a non-Microsoft spreadsheet. This approach also might be easiest to implement if you can extract the data points with known y's into a second table and use that to build the complete table. Of course, the same thing can be accomplished in C, but I'm not familiar with the details.

4) One could write a Sub procedure in VBA that would do the work, but I don't know that this is any better than programming the procedure in C.

[Dav]

Well if you are interpolating between points (the definition and not projectinng beyond you need to find the value above and below your x value and the corresponding y's

If the x's were sorted ascending in the range b6:b16, and te corresponding Ys in c6:c16

To find the x below put in cell e7
=OFFSET(B$5,MATCH($E$5,$B$6:$B$16,1),0)

To find the x above put in cell e8
=OFFSET(B$5,MATCH($E$5,$B$6:$B$16,1)+1,0)

To find the ys below put in cell f7
=OFFSET(C$5,MATCH($E$5,$B$6:$B$16,1),0)

To find the ys above put in cell f8
=OFFSET(C$5,MATCH($E$5,$B$6:$B$16,1)+1,0)


If the X you want a Y for is in e5 the corresponding Y linearly interpolated is

=(E5-E7)/(E8-E7)*(F8-F7)+F7

It is in the attached zipped spreadsheet if that is easier

Regards

Dav

[vic244]

Hi guys,

Thanks for all the excellent answers, finally got it sorted after all the good advice.

One of the best forums I've come across, if I have any other excel queries I'll know where to go!

Cheers...