×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

continuous interpolation in Excel
2

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

RE: continuous interpolation in Excel

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

ec

RE: continuous interpolation in Excel

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)

CODE

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

 

RE: continuous interpolation in Excel

(OP)
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

RE: continuous interpolation in Excel

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

CODE

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

CODE

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.

 

RE: continuous interpolation in Excel

(OP)
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

RE: continuous interpolation in Excel

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: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

RE: continuous interpolation in Excel

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.

RE: continuous interpolation in Excel

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

CODE

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
 

RE: continuous interpolation in Excel

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.
 

RE: continuous interpolation in Excel

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

RE: continuous interpolation in Excel

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

ec

RE: continuous interpolation in Excel

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

RE: continuous interpolation in Excel

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/
 

RE: continuous interpolation in Excel

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources