Excel Equivalent to 1-2-3/Quattro Pro @LINTERP

[Michael G. Scott]

I've got some files in Quattro Pro that use the @LINTERP function. I'm trying
to convert them to Excel, but it seems that Excel doesn't have this function.
I've got a workaround, using :

@slope(y1,y2,x1,x2)*x+@intercept(y1,y2,x1,x2)

but I've got tons of these. Any suggestions?

Help for @LINTERP is below :
==========
@LINTERP - Linear Interpolation

Syntax

@LINTERP(KnownX, KnownY, X)

KnownX = One-dimensional selection containing X values.
KnownY = One-dimensional selection containing Y values corresponding to the X
values in KnownX.
X = Number for which the corresponding Y value is desired.

@LINTERP interpolates a Y value corresponding to X using the XY pairs specified
by KnownX (which contains the X coordinates) and KnownY (which contains the Y
coordinates). If X lies between two values in KnownX, @LINTERP interpolates
using those two values and their respective KnownY counterparts. If X is outside
the range of KnownX, the Y value is extrapolated based on the slope of the line
between the two closest points.

KnownX and KnownY do not have to be the same size. If KnownY is smaller than
KnownX, the last value in KnownY is used as the corresponding Y value for extra
KnownX values. If KnownY is larger than KnownX, its extra values are ignored.

Example
This formula calculates the Y value for the X value 6.7 if the data in the next
figure is used.

@LINTERP(A3..A9,B3..B9,6.7) = 17.5976

A B
1 x values y values
3 -28.345 -9.7821
4 -17.89 -5.6667
5 0.9232 2.891
6 1.212 2.9978
7 4.552 13.67
8 10.75 25.003
9 30.8 33.33

[ALOK JOSHI]

Michael,
Using VBA you can write your own LINTERP function which will take the same
three
parameters and return the linear interpolated value.
Here is a simple form - which does not take into account missing values etc.

Function LINTERP(rngy As Range, rngx As Range, x As Double) As Double
Application.Volatile
Dim dIntercept As Double
Dim dSlope As Double
dIntercept = WorksheetFunction.Intercept(rngy, rngx)
dSlope = WorksheetFunction.Slope(rngy, rngx)
LINTERP = dIntercept + dSlope * x
End Function

HTH
Alok

"Michael G. Scott" <michael.go...@kla-tencor.com> wrote in message
news:3CB62853...@kla-tencor.com...

[Michael G. Scott]

Thanks, Alok

Michael

[Bernard Liengme]

Use Help to find learn about the TREND function.
best wishes
Bernard

[Michael G. Scott]

Thanks Bernard,

Michael

[Tushar Mehta]

There isn't any equivalent function in XL. Neither of the suggestions
you've received will work directly. Your workaround is syntactically
unacceptable to XL.

Your best bet is a User Defined Function (UDF) based on your own
workaround, or on the solution below.

To use your workaround in my example below, you'd have to use
=SLOPE(C7:C8,B7:B8)*targetVal+INTERCEPT(C7:C8,B7:B8). If you use
=SLOPE(yvals, xvals)*targetVal+INTERCEPT(yvals, xvals), you will get
*incorrect* results.

If all your values are strictly greater than the min. x value and
strictly less than the max. x value, then the below suggestion is
correct. If not, you will have to adapt it to cater to boundary
conditions and/or extrapolate.

The spreadsheet I used was:

B C D E F
2 x values y values Target 6.7
3 -28.345 -9.7821
4 -17.89 -5.6667 Specific
5 0.9232 2.891 Result 17.5976031
6 1.212 2.9978
7 4.552 13.67 General
8 10.75 25.003 Interm 5
9 30.8 33.33 Result 17.5976031

where F5 contained the formula =(C8-C7)/(B8-B7)*(6.7-B7)+C7
F8 contained =MATCH(targetVal,xvals,1)
and F9 contained:
=(INDEX(yvals,F8+1)-INDEX(yvals,F8))/(INDEX(xvals,F8+1)-INDEX(xvals,F8))
*(targetVal-INDEX(xvals,F8))+INDEX(yvals,F8)
<watch out for line wrap in the formula above>

The named ranges in the worksheet were:
targetVal =Sheet1!$F$2
xvals =Sheet1!$B$3:$B$9
yvals =Sheet1!$C$3:$C$9

How it all works is below:
The specific solution is probably the easiest to understand. It does a
linear interpolation of the specific y-values that bracket the x-value
of interest to you. The algebraic formula would be y1+(y2-y1)/(x2-x1)*
(x-target-x1), where x1 < x2 and y1 < y2.

The formula in F8 is the first step in generalizing the above specific
solution. F8 identifies the relative location of x1 within the xvals
for any target value (given by the named cell targetVal). F9 takes that
information and does a linear interpolation for that specific value.

Finally, a UDF based on the above algorithm:

Function linearInterp(xvals, yvals, targetVal)
Dim matchVal
With Application.WorksheetFunction
matchVal = .Match(targetVal, xvals, 1)
linearInterp = (.Index(yvals, matchVal + 1) - .Index(yvals,
matchVal)) _
/ (.Index(xvals, matchVal + 1) - .Index(xvals, matchVal)) _
* (targetVal - .Index(xvals, matchVal)) _
+ .Index(yvals, matchVal)
End With
End Function

<watch out for unintended line wrap in the above function>

Important note: There are no safety checks in either the XL function or
VBA UDF. The xvals should be in ascending order, as should the yvals.
Also, the targetVal must be > min. x-val and < max. x-val (*cannot* be
equal). Finally, everything must be a number.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel

In article <3CB62853...@kla-tencor.com>, michael.gordon.scott@kla-
tencor.com says...