Linear interpolation of large data sets

Hey all,

I'm an engineering student, and I was recently asked to crunch a lot of data, but I have an issue. The data that the lab produced is a measure of certain optical properties of amorphous silicon through a range of wavelengths, with wavelength being the independent variable (x) in the data set. A sample of the data looks like this:


210.77812200 1.365183 3.158104
212.36825600 1.383018 3.203131
213.95842000 1.422307 3.261899
215.54863000 1.460486 3.293017
217.13891600 1.482046 3.325932
218.72921800 1.513605 3.334321
220.31958000 1.535114 3.351916

Where the first column is wavelength (nm), the second column is n, and the third column is k. I need to use this data, but to use it, I need to use integer values for wavelength, and was hoping to use linear interpolation. I interpolated a few by hand:


211 1.36767 3.16439
212 1.37889 3.19270

But the process takes a bit of time, and I need to interpolate from 211 nm to 1700 nm. I didn't see a function for this in excel, so does anyone know a faster way to interpolate this data?

Thanks,

--Avren--

Why not just take the actual wavelength and round it?

=ROUND(A1,0)

Hope this helps.

Pete

Sure thing. The relationship looks like this:

First, you take the difference between the desired value and the lower value of the data the desired value resides between, and divide it by the difference between the higher value and the lower value that describe the region the desired value lies within, ie:

(211-210.778)/(212.268-210.778)

This gives the point along the line between the higher and lower values that the value you want lies at as a percentage. You then apply that percentage to the linear relationship of the related data for n and k. A reasonable explanation is found on the wikipedia page for interpolation. This is Linear interpolation.

Even though this is an Excel forum, this might be easiest in QuattroPro if you have it, because QuattroPro had a nice built in linear interpolation function (@LINTERP). I don't know about other spreadsheets, but Microsoft just doesn't seem to want to put a linear interpolation function into Excel.

If you are stuck with Excel, here's how you do it. As you've observed, the formula for linear interpolation is easy once you've isolated the interval that contains your desired x value. So the hard part of linear interpolation is locating the interval containing x.

1) Use the MATCH() function to locate the interval containing x
2) Use the INDEX() function (4 instances) to extract the x and y values at the endpoints of that interval
3) Use your formula (or the TREND() function can also work) to determine y at you given x

I'm not real good at nor fond of trying to build this kind of thing into a single cell. I prefer to spread this out over several cells. I expect someone here is good at nesting these functions in a way that will yield a single cell, if you really need it condensed to one cell.

Soren, the data set itself is certainly not linear by any means, but linear interpolation doesn't need linear data. The assumption here is that, for a small interval (in this case a single nanometer or so) a linear relationship will very closely model the actual curve, and yield a passable estimate of the value. Of course, I could apply my functions to all the intervals, but the intervals the integers can be found on are not regularly represented. I can find 211 and 212 in the first two rows, but only 213 between the second two, making copy and paste impossible, and applying the formulas by hand through the data set beyond time consuming and inefficient.

I love your results, but by copying down through the data, will this compensate for row 2 and 3 being representative of 2 integers (211 and 212) while rows 3 and 4 are only representative of 1 (213)?

Thanks a lot of the help! THis is going to save me quite a bit of time.

So, I'm having trouble with the UDF. I've never loaded one before, and I've been looking at it online, but I'm not getting any information on the subject that I would consider informative. Any help?

Okay, I've loaded the program and it's running, but I'm amending the code you've provided to read:

=LInterp($E2,$A2:$A8,B2:B8)

So the range that's analyzed changes dynamically throughout the spreadsheet.

Ahahaha, never mind, I just needed to adjust the range, this works perfectly. New formula reads:

=LInterp($E2,$A$2:$A$700,B$2:B$700)

I forgot to adjust for my range being larger than the example. We all have Tuesdays like this I guess. Thanks so much for your help.