Does Excel Have an Interpolation Function ?

[orekin]

Hi

I have written a UDF that does simple linear interpolation. The code is pasted below.

Am I reinventing the wheel ? Does excel have a function that does this ?

Cheers
Orekin

Public Function XL_LinearInterpolate(xValue As Double, inputRange As Range) As Double
    Dim xyValues() As Double
    Call CopyRangeToDoubleArray(inputRange, xyValues())
    XL_LinearInterpolate = LinearInterpolate(xValue, xyValues())
End Function

Public Function LinearInterpolate(xValue As Double, xyValues() As Double) As Double

    'Assumes that:
        'dataset() is sorted ascending by first column
        'x vals in first column, y values in second column
    
    Dim i As Long
    
    'If outside range, then flat line it
    If (xValue <= xyValues()(1, 1)) Then
        LinearInterpolate = xyValues()(1, 2)
        Exit Function
    ElseIf (xValue >= xyValues()(UBound(xyValues), 1)) Then
        LinearInterpolate = xyValues()(UBound(xyValues), 2)
        Exit Function
    End If
    
    'Check within the range
    For i = 1 To UBound(xyValues()) - 1 Step 1
        If (xValue >= xyValues()(i, 1) And xValue <= xyValues()(i + 1, 1)) Then
            LinearInterpolate = xyValues(i, 2) + (xyValues(i + 1, 2) - xyValues(i, 2)) * (xValue - xyValues(i, 1)) / ((xyValues(i + 1, 1) - xyValues(i, 1)))
            Exit Function
        End If
    Next i
End Function

 

[PaddyD]

"Am I reinventing the wheel ? Does excel have a function that does this ? "

There are existing functions that enable you to generate the equation for a (polynomial) trendline, and hence extract values from arbitrary positions on it.

See:

http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm