continuous interpolation in Excel
continuous interpolation in Excel
(OP)
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?
RE: continuous interpolation in Excel
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.
RE: continuous interpolation in Excel
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.
RE: continuous interpolation in Excel
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
RE: continuous interpolation in Excel
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
RE: continuous interpolation in Excel
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
RE: continuous interpolation in Excel
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.
RE: continuous interpolation in Excel
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)
RE: continuous interpolation in Excel
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))
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
=(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