I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

Interpolate

If you have a given set of known (x, y) points and you need to know y-parts of other given x parts then you need to interpolate. It is similar to filling gaps of a table:

Robins_Forest_Tax06

Function Interp(vX As Variant, vY As Variant, _
           vT As Variant, _
           Optional sType As String = "Linear", _
           Optional bExtrapolate As Boolean = True, _
           Optional sExtraType As String) As Variant
'Interpolates y-values for target values vT with known
'y-values vY and known x-values vX with type sType.
'sType can be:
'Const or C
'Linear or L
'LinearInVariance or LIV
'Extrapolation will be done if bExtrapolate is TRUE.
'Extrapolation type sExtraType defaults to sType if empty.
'Values in vX must be 'in ascending order. #VALUE! error
'indicates illegal sType, #NUM! error indicates that
'extrapolation has been switched off and #N/A tells you
'that x-values are not given in increasing order.
'Reverse(moc.liborplus.www) V0.4 PB 17-May-2009
Dim i As Long, iX As Long, iT As Long, k As Long
Dim vTk, vXi
Dim sT As String 'Type of inter- or extrapolation
Dim sEType As String 'Extrapolation type

On Error Resume Next
iX = vX.Count
iX = UBound(vX)
iT = vT.Count
iT = UBound(vT)
On Error GoTo 0
ReDim vR(1 To iT) As Variant
With Application.WorksheetFunction
If iX < 2 Then
    Interp = CVErr(xlErrNA)
    Exit Function
Else
    For k = 2 To iX
        If vX(k) <= vX(k - 1) Then
            Interp = CVErr(xlErrNA)
            Exit Function
        End If
    Next k
End If
If sExtraType = "" Then
    sEType = sType 'Same as interpolation type
Else
    sEType = sExtraType
End If
For k = 1 To iT
    i = 0
    vTk = 0
    vXi = 0
    On Error Resume Next
    i = .Match(vT(k), vX, 1)
    vTk = vT(k)
    vXi = vX(i)
    On Error GoTo 0
    If Not bExtrapolate And _
        (i = 0 Or (i = iX And vTk <> vXi)) Then
        vR(k) = CVErr(xlErrNum)
    Else
        sT = sType 'Set to interpolation type
        If i = 0 Then
            i = 1
            sT = sEType 'Set to extrapolation type
        End If
        If i = iX Then
            i = i - 1
            If vTk <> vXi Then
                sT = sEType 'Set to extrapolation type
            End If
            If sT = "C" Or sT = "Const" Then i = i + 1
        End If
        Select Case sT
        Case "C", "Const"
            vR(k) = .Index(vY, i)
        Case "L", "Linear"
            vR(k) = .Index(vY, i) + (vTk - .Index(vX, i)) _
                * (.Index(vY, i + 1) - .Index(vY, i)) _
                / (.Index(vX, i + 1) - .Index(vX, i))
        Case "LIV", "LinearInVariance"
            On Error Resume Next
            vR(k) = Sqr(.Index(vY, i) ^ 2# + (vTk - .Index(vX, i)) _
                * (.Index(vY, i + 1) ^ 2# - .Index(vY, i) ^ 2#) _
                / (.Index(vX, i + 1) - .Index(vX, i)))
            On Error GoTo 0
        Case Else
            Interp = CVErr(xlErrValue)
            Exit Function
        End Select
    End If
Next k
If TypeName(Application.Caller) = "Range" Then
    If Application.Caller.Rows.Count > _
        Application.Caller.Columns.Count Then
        vR = .Transpose(vR)
    End If
End If
End With
Interp = vR
End Function

If you cannot or if you do not want to use macros, you might want to interpolate with these worksheet functions (poor you):

Poor man's interpolation (PERCENTILE / PERCENTRANK approach)

Poorest man's interpolation (piecewise TREND approach)

If you are lucky and your slopes between adjacent points are monotonously (de/in)creasing without a sign change you can apply the funnily short MiniMax Interpolation.