continuous interpolation in Excel 2

[EricClapton]

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

 

[cowski]

Are you referring to this thread?
thread770-13682

 

[EricClapton]

Hi cowski,
Yes I'm looking for the macro created by ivymike on 21 Nov 01 11:08.

ec

 

[chemebabak]

You can try this easy example or check out the attached file:

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)

Private Sub InterpolateButton_Click()
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

 

[EricClapton]

Thanks chemebabak,
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

 

[chemebabak]

http://en.wikipedia.org/wiki/Linear_interpolation

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

Private Sub InterpolateButton_Click()
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

Private Sub InterpolateButton_Click()
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.

 

[EricClapton]

chemebak, I know my data are not linear and that's why I find it difficult solve the problem. I'll have a closer look and try to find the best solution suitable for the data set.

ec

 

[IRstuff]

I think you need to revisit the whole premise. The regression coefficient that results from most attempts at a fit is extremely poor, indicating that whatever the function is, it's pretty weak. Conversely, any attempt to interpolate is just going to give you a value with lots of error baggage.

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
Chinese prisoner wins Nobel Peace Prize

 

[corus]

Without doing the full statistical analysis I think you'd find that the most 'significant' relationship for the whole data is a linear fit. Anything beyond that, such as a nth degree polynomial, would be meaningless. Unless there is a known reason for the scatter then I'd just use a linear fit across the whole data and use that relationship whatever the value of x.

 

[yakpol]

I've been using this User-defined VBA function for years, works great for ascending and descending arrays.

Public Function INTER1(Xval As Double, x As Range, Y As Range)
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

 

[aabumady]

try to use the attached file.

 

[77JQX]

I solve these issues without a marco. For the following data:

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.

 

[IDS]

Some more alternative "on-sheet" formulas here:

http://newtonexcelbach.wordpress.com/2011/09/04/spline-interpolation-alternatives/

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/

 

[77JQX]

Dang. "Content blocked." I guess if I want to get smarter, I'll have to try from home. Thanks, Doug.

 

[EricClapton]

Thanks IDS, I think I got it know. Step-by-step instructions are very useful for ammateurs in Excel.

ec

 

[yakpol]

After so many years... I found a right function for linear interpolation in excel: =FORECAST(valX, rangeY, rangeX)

 

[IDS]

yakpol - Forecast works the same as Linest; it gives a best fit line through the data, not a piecewise linear interpolation through adjacent points.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[yakpol]

IDS,
You are right, I mistakenly tested it on linear data...