×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

Interpolate Help
2

Interpolate Help

Interpolate Help

(OP)
In Excel, I want to subtract cell J20- cell A14, and lets call this variable W. W correlates with the column N data from SET1. I want to interpolate column M for SET1. W correlates with the column N data from SET2. I want to interpolate column M for SET2. Let's call the results X1 and X2 for SET1 and SET2, respectfully. Then I want to solve X2-X1.

SET1 and SET2 represent the coordinates of the left and right sides of an object, and I want to calculate the horizontal distance at a certain vertical point.

cell J20 = 54
cell A14 =5

SET1 cells M15:N21:
3 54.000
0 50.000
1 50.000
1 44.000
0 44.000
0 24.000
0 0.000

SET 2 cells M29:N33:
17 27.000
14 33.000
11 48.000
11 54.000
3 54.000

I have tried this formula:
=INDEX(M29:M33,MATCH(J20-A14,N29:N33,1)) - INDEX(M15:M21,MATCH(J20-A14,N15:N21,1))

The result should be 10, but I keep getting 11. Any ideas?

RE: Interpolate Help

Hi,

Your set 1 N values are descending, while set 2 is ascending.

Therefore your second MATCH ought to have a -1 Match Type.

Tip 2: with formula problems, test from the inside out.
I started by looking at the MATCHs as stand alone functions with the given values and ranges. BINGO!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Interpolate Help

You may come up with a long cell formula for linear interpolation. Excel still does not have a good built-in function to do this simple task. Or you can use VBA function presented below.

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

  
If Xval < x(1) Or Xval > x(x.Count) Then
    INTER1 = 0
Else
    Nrow = Application.WorksheetFunction.Match(Xval, x, IIf(x(1) > x(x.Count), -1, 1))
    If Nrow = x.Count Then
        INTER1 = Y(Nrow)
    Else
        If Abs(x(Nrow + 1) - x(Nrow)) > 0.0000001 Then
            INTER1 = Y(Nrow) + (Xval - x(Nrow)) / (x(Nrow + 1) - x(Nrow)) * (Y(Nrow + 1) - Y(Nrow))
        Else
            INTER1 = Y(Nrow)
        End If
    End If
End If

End Function 

RE: Interpolate Help

(OP)
Thanks Skip, that worked.

RE: Interpolate Help

@LOTE, your use of "interpolate" is misleading, since the use of the MATCH() and INDEX() functions to return a value, when the Match Type is +/- 1, is not an interpolation, that is calculating, for instance, an estimated linear value between two set-values based on an x: 0 < x < 1.

You might select a set-value using Match Type +/- 1 but that's early in the process of interpolation.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Interpolate Help

(OP)
My intent was to interpolate. I messed around with the formula quite a bit more and this is my final version for the X2 component:
=FORECAST(V19,INDEX(M27:M33,MATCH(V19,N27:N33,1)):INDEX(M27:M33,MATCH(V19,N27:N33,1)+1),INDEX(N27:N33,MATCH(V19,N27:N33,1)):INDEX(N27:N33,MATCH(V19,N27:N33,1)+1))

Gosh, you would think Microsoft would have an easier way to do this.

RE: Interpolate Help

I found this on the web a long time ago, and it works great as a UDF. It's full of examples of how to use it. You probably want the 'Interp' function

CODE -->

Function Interp(TableRange As Variant, RowVal As Double, ColOffset As Long) As Double
Dim NoRows As Long, i As Long
Dim ROffset As Long
Dim XN As Double, Xp As Double, YN As Double, Yp As Double

    If TypeName(TableRange) = "Range" Then TableRange = TableRange.Value2

    ' Find table size
    NoRows = UBound(TableRange)
    Xp = TableRange(2, 1)
    XN = TableRange(3, 1)

    If XN > Xp Then
        ' Find row offset
        For i = 2 To NoRows
            If RowVal < TableRange(i, 1) Then
                ROffset = i
                Exit For
            End If
        Next i
    ElseIf XN < Xp Then
        For i = 2 To NoRows
            If RowVal > TableRange(i, 1) Then
                ROffset = i
                Exit For
            End If
        Next i
    End If

    XN = TableRange(ROffset, 1)
    Xp = TableRange(ROffset - 1, 1)

    YN = TableRange(ROffset, ColOffset + 1)

    Yp = TableRange(ROffset - 1, ColOffset + 1)


    Interp = Yp + ((RowVal) - (Xp)) / ((XN) - (Xp)) * (YN - Yp)

End Function 

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! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close