Does Excel Have an Interpolation Function ?

orekin

Board Regular
Joined
Jun 1, 2004
Messages
50
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

Code:
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,118,525
Messages
5,572,641
Members
412,478
Latest member
MakeItWorkVBA
Top