×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Interpolate Help2

## 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,

Just traded in my OLD subtlety...
for a NUance!

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!