Interpolate Help 2

[LOTE]

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?

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[yakpol]

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.

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

 

[LOTE]

Thanks Skip, that worked.

 

[SkipVought]

@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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LOTE]

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.

 

[Cobra17]

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

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

 

[IDS]

Cobra17 - Hmm, that looks familiar.
Checks spreadsheet library.
There it is; glad you found it useful

That code and related stuff can be found at:

https://newtonexcelbach.com/2012/10/02/daily-download-16-intersections-interpolations-and-rotations/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/