sbSumLast

Here is a VBA function which sums the last non-empty cells of a given range:
Please read my Disclaimer.
Function sbSumLast(r As Range, _
    Optional ByVal lCount As Long = 5) As Double
'Sums up last lCount values of a given range.
'0 count as zero, "" will be skipped.
'Reverse("moc.LiborPlus.www") V0.2 PB 08-Feb-2011
Dim i As Long, dSum As Double
On Error GoTo errhdl
i = r.Count
Do While lCount > 0
    If r(i) <> "" Then
        dSum = dSum + r(i)
        lCount = lCount - 1
    End If
    i = i - 1
Loop
fctexit: sbSumLast = dSum
Exit Function
errhdl: Resume fctexit
End Function
 A similar function which calculates the average of the last n cells is sbAverageLast.
Function sbSpecSumSmallest5ofLast10(r As Range) As Double
'Sums up smallest 5 of last 10 values of a given range.
'0 count as zero, "" will be skipped. If there are only
'1 or 2 values, smallest will be returned, if there are
'3 or 4 values, sum of smallest 2, etc.
'Reverse("moc.LiborPlus.www") V0.1 PB 08-Feb-2011
Dim i As Long, j As Long, k As Long, m As Long, lCount As Long
Dim dSum As Double
Dim dSmallest(1 To 10) As Double
On Error GoTo errhdl
lCount = 10
i = r.Count
Do While lCount > 0
    If r(i) <> "" Then
        j = 1
        Do While j <= k
            If r(i) < dSmallest(j) Then Exit Do
            j = j + 1
        Loop
        k = k + 1
        For m = k To j + 1 Step -1
            dSmallest(m) = dSmallest(m - 1)
        Next m
        dSmallest(j) = r(i)
        lCount = lCount - 1
    End If
    i = i - 1
Loop
fctexit:
For i = 1 To Int((k + 1) / 2)
    dSum = dSum + dSmallest(i)
Next i
sbSpecSumSmallest5ofLast10 = dSum
Exit Function
errhdl: Resume fctexit
End Function

sbSplit

sbSumMyFormat

Last modified 2yr ago

Copy link