continuous interpolation in Excel
continuous interpolation in Excel
(OP)
I would appreciate if ivymike could post the 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.
Thanks
ec
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.
Thanks
ec





RE: continuous interpolation in Excel
thread770-13682: continuous interpolation in Excel
RE: continuous interpolation in Excel
Yes I'm looking for the macro created by ivymike on 21 Nov 01 11:08.
ec
RE: continuous interpolation in Excel
Let us say that I6 is your X value cell and J6 will be the Y value
X1:
I5 =HLOOKUP(I6,$E$5:$E$22,MATCH(I6,$E$5:$E$22,1),TRUE)
X2:
I7 =HLOOKUP(I6,$E$5:$E$22,MATCH(I6,$E$5:$E$22,1)+1,TRUE)
Y1:
J5 =VLOOKUP(I5,$E$5:$F$22,2,TRUE)
Y2:
J7 =VLOOKUP(I7,$E$5:$F$22,2,TRUE)
Y value
J6 =J5+(I6-I5)/(I7-I5)*(J7-J5)
CODE
Dim XY_values As Range
X_value = Range("B6")
Set XY_values = Range("$E$5:$F$22")
X_value_row = Excel.WorksheetFunction.Match(X_value, XY_values(, 1), True)
X1value = Excel.WorksheetFunction.HLookup(X_value, XY_values, X_value_row, True)
X2value = Excel.WorksheetFunction.HLookup(X_value, XY_values, X_value_row + 1, True)
Y1value = Excel.WorksheetFunction.VLookup(X1value, XY_values, 2, True)
Y2value = Excel.WorksheetFunction.VLookup(X2value, XY_values, 2, True)
Y_value = Y1value + (Y2value - Y1value) * (X_value - X1value) / (X2value - X1value)
Range("C6").Value = Y_value
End Sub
RE: continuous interpolation in Excel
I'll test it tomorrow; I'm a bit tired now.
However, does this interpolation function generates values at specific even steps?
My data look like this:
depth column A (in m)
2
2.58
3.16
3.74
4.32
4.9
5.48
6.06
6.64
7.22
7.8
8.38
8.96
9.54
10.12
and variable beam attenuation (m-1) in column B
1.810
1.935
1.854
1.789
1.785
1.763
1.785
1.691
1.666
1.590
1.742
1.637
1.685
1.710
1.638
So, the function required should calculate interpolated beam attenuation at depths 2, 3, ..., 10.
Does the function given provides this?
ec
RE: continuous interpolation in Excel
Eric, this spreadsheet works with linear interpolation, which has been done for years. Just make sure you correct the VBA code if you don't want to use the simple excel method.
Change from
CODE
Dim XY_values As Range
X_value = Range("B6")
Set XY_values = Range("$E$5:$F$22")
X_value_row = Excel.WorksheetFunction.Match(X_value, XY_values(, 1), True)
X1value = Excel.WorksheetFunction.HLookup(X_value, XY_values, X_value_row, True)
X2value = Excel.WorksheetFunction.HLookup(X_value, XY_values, X_value_row + 1, True)
to
CODE
Dim XY_values, X_values As Range
X_value = Range("B6")
Set XY_values = Range("$E$5:$F$22")
Set X_values = Range("$E$5:$E$22")
X_value_row = Excel.WorksheetFunction.Match(X_value, X_values, True)
X1value = Excel.WorksheetFunction.HLookup(X_value, X_values, X_value_row, True)
X2value = Excel.WorksheetFunction.HLookup(X_value, X_values, X_value_row + 1, True)
By the way, your data is not linear. Your data bounces all over the place. The closest I could find was a curve fit for a 6th order polynomial.
y = -0.0002x^6 + 0.0066x^5 - 0.095x^4 + 0.7062x^3 - 2.8247x^2 + 5.6816x - 2.5701
In either case you need to review your data before you interpolate.
RE: continuous interpolation in Excel
ec
RE: continuous interpolation in Excel
You're getting less than 0.35 p-p for a factor of 5 change in the abcissa; that's really weak. If you just arbitrarily pick y=1.76, you'd only be off by about 10% across the entire range.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
CODE
Dim Nrow%
Nrow = Application.WorksheetFunction.Match(Xval, x, IIf(x(1) > x(x.Count), -1, 1))
INTER1 = Y(Nrow) + (Xval - x(Nrow)) / (x(Nrow + 1) - x(Nrow)) * (Y(Nrow + 1) - Y(Nrow))
End Function
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
1 A B
2 1 10
3 2 50
4 3 80
5
6 new x new y
7 2.4 62
The formula in B7 is:
=TREND(OFFSET($B$1,MATCH(A7,$A$2:$A$4,1),0,2,1),OFFSET($A$1,MATCH(A7,$A$2:$A$4,1),0,2,1),A7)
This formula picks the values that bracket the "new x" value, fit an exact line through those two points, and returns the corresponding y. Easy peasy.
RE: continuous interpolation in Excel
ht
including some for cubic interpolation, rather than linear.
It also includes VBA versions, which in my opinion are even easy-peasier than gargantuan on-sheet formulas.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
ec
RE: continuous interpolation in Excel
RE: continuous interpolation in Excel
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: continuous interpolation in Excel
You are right, I mistakenly tested it on linear data...