Here is my interpretation of using Interpolation for Missing Buckets rolled up into an Excel VBA UDF. This UDF Interpolation Function provides Linear Interpolation of missing buckets with the ability to avoid inflation by rebalancing the values if required. It also allows the use of a Fixed Start value and a Fixed End value with two coefficients to adjust these values further, again, if required. Copy & Paste this Code into any Standard Code Module Back · Purchase

Public Function Interpolation(ByVal Known_ys As Range, Optional ByVal FixedStart As Boolean = False, Optional ByVal FixedEnd As Boolean = False, Optional ByVal StartCoefficient As Double = 1, Optional ByVal EndCoefficient As Double = 1, Optional Rebalance As Boolean = True) As Variant
    Dim Anchor As Boolean
    Dim Share As Double
    Dim TempShare As Double
    Dim SUM(1)
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    Dim FirstIndex As Long
    Dim NextIndex As Long
    Dim LastFirstIndex As Long
    Dim LastNextIndex As Long
    Dim V As Variant
 
    V = Known_ys
    FirstIndex = 1
    NextIndex = 1
    Anchor = False
    
    For X = 1 To UBound(V, 2)
        If V(1, X) <> Empty Then V(1, X) = Val(V(1, X))
        If V(1, X) <= 0 Then
            V(1, X) = Empty
        End If
    Next X
    SUM(0) = Application.WorksheetFunction.SUM(V)
    
    For X = 1 To UBound(V, 2)
              
        If V(1, X) <= 0 Then
            If Not Anchor Then
                If NextIndex > FirstIndex Then FirstIndex = NextIndex
                NextIndex = X
                Anchor = True
            End If
        End If
              
        If V(1, X) > 0 Then
            Z = Z + 1
            If Anchor Then
                NextIndex = X
                Anchor = False
            Else
                FirstIndex = X
            End If
    
        End If
              
        If V(1, X) > 0 And NextIndex > FirstIndex Then
            Anchor = False
            If V(1, NextIndex) > V(1, FirstIndex) Then
                If V(1, FirstIndex) <= 0 Then
                    Share = (V(1, NextIndex) - V(1, FirstIndex)) / ((NextIndex - FirstIndex) + 1)
                Else
                    Share = (V(1, NextIndex) - V(1, FirstIndex)) / ((NextIndex - FirstIndex))
                End If
            Else
                Share = (V(1, FirstIndex) - V(1, NextIndex)) / ((NextIndex - FirstIndex))
            End If
    
            TempShare = V(1, FirstIndex)
            If V(1, FirstIndex) <= 0 Then
    
                For Y = FirstIndex To NextIndex - 1
                    If FixedStart Then
                        V(1, Y) = V(1, NextIndex) * StartCoefficient
                    Else
                        V(1, Y) = (V(1, Y) + TempShare + Share) * StartCoefficient
                        TempShare = TempShare + Share 'V(1, Y)
                    End If
                Next Y
    
            ElseIf V(1, NextIndex) > V(1, FirstIndex) Then
                For Y = FirstIndex + 1 To NextIndex - 1
                    V(1, Y) = (V(1, Y) + TempShare + Share)
                    TempShare = TempShare + Share 'V(1, Y)
                Next Y
            Else
                TempShare = V(1, NextIndex)
                For Y = NextIndex - 1 To FirstIndex + 1 Step -1
                    V(1, Y) = (V(1, Y) + TempShare + Share)
                    TempShare = TempShare + Share 'V(1, Y)
                Next Y
            End If
    
        Else
            TempShare = 0
            NextIndex = X
            LastFirstIndex = FirstIndex
            LastNextIndex = NextIndex
        End If
    
    Next X
    
    If V(1, NextIndex) <= 0 Then
        Share = (V(1, FirstIndex) - V(1, NextIndex)) / ((NextIndex - FirstIndex + 1))
        TempShare = 0
        For Y = FirstIndex + 1 To NextIndex
            If FixedEnd Then
                TempShare = V(1, FirstIndex) * EndCoefficient
                If TempShare < 0 Then TempShare = 0
                V(1, Y) = TempShare
            Else
                TempShare = (V(1, Y - 1) - Share) * EndCoefficient
                If TempShare < 0 Then TempShare = 0
                V(1, Y) = TempShare
            End If
        Next Y
    End If
    SUM(1) = Application.WorksheetFunction.SUM(V)
        
    If Rebalance Then
        For X = 1 To UBound(V, 2)
            V(1, X) = V(1, X) / SUM(1) * SUM(0)
        Next X
    End If
    
    Interpolation = V
End Function

Function information is added to the Macro Table in the 'ThisWorkbook' Sheet giving you intellisense about the Named Arguments in the 'fx' (Formula Box) in Excel. Copy the Code below into the 'ThisWorkbook' Sheet Code Module:

Private Sub Workbook_Open()

    Dim FunctionName As String
    Dim FunctionDesc As String
    Dim ArgDesc(1 To 6) As String

    FunctionName = "Interpolation"
    FunctionDesc = "Performs Linear Interpolation on Known_ys with the ability to fix the Start & End values and rebalance any inflation"
    ArgDesc(1) = "Provide the Known_ys that will be used for Linear Interpolation"
    ArgDesc(2) = "Optional: Choose whether to use a Fixed Start value as opposed to Interpolation.  True = yes, False = no, default = False"
    ArgDesc(3) = "Optional: Choose whether to use a Fixed End value as opposed to Interpolation.  True = yes, False = no, default = False"
    ArgDesc(4) = "Optional: Start Coefficient used to multiply the Start value, default = 1"
    ArgDesc(5) = "Optional: End Coefficient used to multiply the End value, default = 1"
    ArgDesc(6) = "Optional: Rebalance used to evenly distribute the inflation due to Interpolation, default = True"
    Application.MacroOptions Macro:=FunctionName, Description:=FunctionDesc, Category:=vbNullString, ArgumentDescriptions:=ArgDesc

End Sub

Use the UDF like this in any Worksheet in Excel for a range of any size (Example shown Range := $D28:$R28, 15 values):

' Interpolation (unbalanced)
=Interpolation($D28:$R28,FALSE,FALSE,1,1,FALSE)

' Interpolation (rebalanced)
=Interpolation($D28:$R28)

Here are the Actuals values used for this demonstration detailing the Interpolation results for both unbalanced and rebalanced values. Please Note: zeros can be zero (0) or blank - the algorithm treats them as similar. Also negatives are removed by the algorithm and treated as zero / blank. I will underline the Interpolated values for the unbalanced series so that you can clearly see the values that have been Interpolated for the missing buckets. One last point is that for this algorithm we cannot start with a zero (0) hence we always divide by 'Gap' + 1 to ensure a non-zero start i.e. for 4 missing buckets and a first value of 8, we would Interpolate: 1.6, 3.2, 4.8, 6.4 and then 8, not: 0, 2, 4, 6, 8 or: 1, 2, 4, 6, 8

Series	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Actuals	0	0	4		-1	8	11	3		6		9	7
Interpolation (unbalanced)	1.3	2.7	4	5.3	6.7	8	11	3	4.5	6	7.5	9	7	4.7	2.3
Interpolation (rebalanced)	0.8	1.5	2.3	3.1	3.9	4.6	6.4	1.7	2.6	3.5	4.3	5.2	4.0	2.7	1.3

Chart demonstrating the VBA UDF Linear Interpolation Function on a range of 15 values, Fixed Start := False, Fixed End := False, Start Coefficient := 1, End Coefficient := 1, Rebalance := False. The Actuals series SUM to 48 and the Interpolation series SUM to 83 (unbalanced and with inflation)

Chart demonstrating the VBA UDF Linear Interpolation Function on a range of 15 values, Fixed Start := False, Fixed End := False, Start Coefficient := 1, End Coefficient := 1, Rebalance := True. The Actuals series and the Interpolation series both SUM to 48 (rebalanced without inflation)

---

Here is another Linear Interpolation using An Excel VBA UDF. This UDF provides Linear Interpolation of missing buckets by coearsing the first and last buckets to 1 if they are missing and/or they are negative or zero (0). This Interpolation routine uses a Slope and Intercept calculation to Interpolate between any Series of 'Known_ys' against any Series of 'Known_xs', meaning it can fit the Interpolation values for sequential or disparate periods, for example, given Known_ys of '2, 0 and 4' and 'Known_xs' periods of '7, 12 and 20' the period '12' of the 'Known_xs' for the value '0' will be interpolated to '2.77'. This Interpolation routine will inflate the final values as it does not rebalance the weightings of the values following Interpolation. Copy & Paste this Code into any Standard Code Module Back · Purchase

Public Function LinearInterpolate(ByVal Known_ys As Range, ByVal Known_xs As Range) As Variant
 
    Dim First As Long
    Dim Last As Long
    Dim X As Long
    Dim Y As Long
    Dim SizeOfYS As Long
    Dim Slope As Double
    Dim Intercept As Double
    Dim YS As Variant
    Dim XS As Variant

    YS = Known_ys
    SizeOfYS = UBound(YS, 2)
    XS = Known_xs

    For X = 1 To SizeOfYS
        If YS(1, X) <> Empty Then YS(1, X) = Val(YS(1, X))
        If YS(1, X) <= 0 Then
            YS(1, X) = Empty
        End If
        If YS(1, 1) <= 0 Then YS(1, 1) = 1
        If YS(1, SizeOfYS) <= 0 Then YS(1, SizeOfYS) = 1
    Next X

    First = 0
    For X = 1 To SizeOfYS
        If YS(1, X) <= 0 Then
            If First = 0 Then First = X - 1
        ElseIf YS(1, X) > 0 And First > 0 Then
            Last = X
            For Y = First + 1 To X - 1
                Slope = (YS(1, Last) - YS(1, First)) / (XS(1, Last) - XS(1, First))
                Intercept = YS(1, First) - Slope * XS(1, First)
                YS(1, Y) = (Slope * XS(1, Y) + Intercept)
            Next Y
            First = 0
        End If
    Next X

    LinearInterpolate = YS

End Function

Function information is added to the Macro Table in the 'ThisWorkbook' Sheet giving you intellisense about the Named Arguments in the 'fx' (Formula Box) in Excel. Copy the Code below into the 'ThisWorkbook' Sheet Code Module:

Private Sub Workbook_Open()

    Dim FunctionName As String
    Dim FunctionDesc As String
    Dim ArgDesc(1 To 2) As String

    FunctionName = "LinearInterpolate"
    FunctionDesc = "Performs Linear Interpolation on Known_ys with the ability to Interpolate any period.  Start and End are set to 1 if negative/zero/blank"
    ArgDesc(1) = "Provide the Known_ys that will be used for the Linear Interpolation, typically the Actuals"
    ArgDesc(2) = "Provide the Known_xs that will be used for Linear Interpolation, typically the Periods"
    Application.MacroOptions Macro:=FunctionName, Description:=FunctionDesc, Category:=vbNullString, ArgumentDescriptions:=ArgDesc

End Sub

Use the UDF like this in any Worksheet in Excel for a range of any size:

' Known_ys = Actuals, '$D28:$S28' and Knowyn_xs = Periods (sequential), '$D27:$S27' 
=LinearInterpolate($D28:$S28,$D27:$S27)

Here are the Actuals values used for this demonstration detailing the Interpolation results on Sequential Time Periods. I will underline the Interpolated values for the series so that you can clearly see the values that have been Interpolated for the missing buckets

Series	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
Actuals	2		3	2	2		3	3		6	2	2			11	12
Interpolate	2	2.5	3	2	2	2.5	3	3	4.5	6	2	2	5.0	8.0	11	12

Chart demonstrating the VBA UDF Linear Interpolate Function on a range of 16 sequential values

Here are the Actuals values used for this demonstration detailing the Interpolation results on Disparate Time Periods. I will underline the Interpolated values for the series so that you can clearly see the values that have been Interpolated for the missing buckets

Series	7	12	20
Actuals	2		4
Interpolate	2	2.77	4

Chart demonstrating the VBA UDF Linear Interpolate Function on a range of 3 disparate values