Linear interpolation

[Paul_Hossler]

If it's not necessary to 'roll your own' why not use the built in Slope() and Intercept() functions?

They seem to handle your gap case better. I also did a trivial straight line case

Capture.JPG

Option Explicit

Function Interpolate(oldYears As Range, oldRates As Range) As Variant
    Dim i As Long
    Dim A() As Variant
    Dim X As Variant, Y As Variant
    Dim m As Double, b As Double
        
    With Application.WorksheetFunction
        X = .Transpose(oldYears)
        Y = .Transpose(oldRates)
        
        ReDim A(1 To X(UBound(X)), 1 To 2)
        
        m = .Slope(Y, X)
        b = .Intercept(Y, X)
    End With
        
    For i = LBound(A, 1) To UBound(A, 1)
        A(i, 1) = I
        A(i, 2) = m * i + b
    Next i
Interpolate = A
               
End Function