continuous interpolation in Excel 3

[dgg]

I am wondering if there is a simpler way to interpolate between a large list of numbers at specified intervals. For example, I have a large series of data points at 0.6 metre intervals. I want to interpolate between the points at 0.5 metre intervals. The problem arises when two data points fall within the same interval. How do I interpolate without going into the formulas and manually editing them?

 

[Terzaghi]

Are you talking about linear interpolation? Even if you are not, why not just come up with the equation of the line that describes your data points. You can then plug in any x or whatever and get your desired results at any point?

I sometimes plot the data on a chart, find the best trend line that models the data, have excel display the equation on the chart, and then use that equation for interpolation.

 

[ivymike]

I recently faced a similar problem. Here's the problem and how I solved it:

I had about 1620 cylinder pressure vs crank angle data points from a client, where crank angle was not given in steady increments, but was always increasing. Sometimes the angle would go up by 0.1 degrees, sometimes by 0.73, etc. I had to convert this information into 500 (or less) evenly spaced data points representing cyl pressure over 720 degrees of crankshaft rotation. I wrote a macro that does the following:

1) Receive as input the complete list of data (x and y), and store it in an array

2) receive as input the X value for which a Y value is desired

3) loop through the list of data to find two input X locations that bracket the desired X location

4) interpolate linearly between those two locations to get the Y value at the new location

5) output the Y value corresponding to that X location.

Then calculating the evenly spaced values was a simple matter of calling (from the spreadsheet) my function, with an input range giving known X and Y values, and a single cell containing the desired X value. The output was the desired Y value. The only tricky part was that the end of my input data was supposed to be the same as the beginning, so I had to tailor my code to allow the desired point to fall between the last point and the first one. Another note would be that you should make sure your data is in order (sorted) before running, or set up your code to sort the input values before further processing.

 

[GregLocock]

I have had great success with linear interpolation, using the data offset vertically by one cell, in effect, and then using VLOOKUP to test for a higher and lower limit for each desired interpolation point.

Unfortunately the dog ate my homework (well, they've replaced this computer) so I don't have it with me, but I think that should be enough to get an adept Excel user going!
Cheers

Greg Locock

 

Try this code in a module, it will allow you to Interpolate between points, quite handy.


Function Interpolation(X1, Y1, X2, Y2, X3)

Dim dX, dY, dYdX, Y3

dX = X2 - X1
dY = Y2 - Y1
dYdX = dY / dX
Interpolation = (((X3 - X1) * dYdX) + Y1)

End Function

 

[yakpol]

Personally, I would prefer to write VBA function to do linerar interpolation, but there's a way to use just spereadsheet formulas as well.

Name the range containing locations of data points as X (has to be in descending or ascending order, single column or single row) and the range of corresponding values as Y.

Cell

A1

- input value for X-location
Cell

B1

- interpolated value of Y
Cell

C1

- index value (used to shorten interpolation formula)

Cell

C1

formula checks the order of X-table and finds the index of closest to input value:

=MATCH(A1,X,IF(ISNA(LOOKUP(A1,X)),-1,1))

Cell

B1

formula performs linear interpolation:

=INDEX(Y,C1)+(A1-INDEX(X,C1))/(INDEX(X,C1+1)-INDEX(X,C1))*(INDEX(Y,C1+1)-INDEX(Y,C1))

You can create a table copying cells A1:C1 down.
Hope it will work for you. Email me if you having problems and I'll send you a file.

http://yakpol.net

 

[ivymike]

Karmond,

That function will allow you to interpolate if you already know what x1, x2, y1, and y2 are. The original question (I think) was referring to a situation where x1, x2, y1, and y2 are in a list of values, and need to be identified automatically for a given x3.

Greg,

Thanks for the info on Vlookup. That's a very useful worksheet function. I can't figure out how to make it cough up the value one step larger than the search value though (it defaults to the next smaller value). Thanks for showing me that one though, I'll definitely be using it in the future.

Here's another solution that I came up with (inspired by Greg's post):

if we have a spreadsheet where
* column A contains known x values
* column B contains known Y values
* col D contains X values for which a Y value is desired
* values in column x are in ascending order
* cols A, B, and D have data in rows 2 thru 555

then:

=match(D2,$A$2:$A$555,1) gives the row number within 2-555 where the X value just lower than the X value in D2 is located (we'll store this value in E2)

=INDEX($A$2:$A$555,E2,1) gives the value of "lower X" (we'll store it in F2)

=INDEX($A$2:$A$555,E2+1,1) gives the value of "upper X" (we'll store it in G2)

=INDEX($B$2:$B$555,E2,1) gives the value of "lower Y" (we'll store it in H2)

=INDEX($B$2:$B$555,E2+1,1) gives the value of "upper Y" (we'll store it in I2)

=(D2-F2)/(G2-F2)*(I2-H2)+H2 gives us the value we are looking for.

alternatively, we could condense all of those equations into one bulky one:

=(D2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))/(G2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))*(INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1)+1,1)-INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1))+INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1)

whew, that's a mouthful.

 

[ivymike]

yakpol,

looks like we were typing our solutions at the same time - I was wondering why I didn't see yours there before I posted.

there's a typo in mine that I felt obligated to correct (I didn't substitute the formula in for G2 in one spot)

the correct messy formula is (I believe)

=(D2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))/(INDEX($A$2:$A$555,E2+1,1)-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))*(INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1)+1,1)-INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1))+INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1)

 

[yakpol]

ivymike,
We, probably, posted the solutions in the same time. I tried to accomodate ascending and descending order of data points. The functions MATCH() and LOOKUP() work inconsistently with descending data, more bulletproof solution to my previous post:

Cell C1 formula:

=MATCH(A1,X,IF(ISNA(MATCH(A1,X,-1)),1,-1))

 

[ivymike]

nope, I missed an E2 as well. I give up.

 

[ivymike]

hey, that isna is pretty neat. I really learned a lot today.

 

I used the messy formula
=(D2- INDEX($A$2:$A$57,E2,1))/( INDEX($A$2:$A$57,E2+1,1)- INDEX($A$2:$A$57,E2,1))*( INDEX($B$2:$B$57,E2+1,1)- INDEX($B$2:$B$57,E2,1))+ INDEX($B$2:$B$57,E2,1)
And that worked
Jacob