sbSumLast

Here is a VBA function which sums the last non-empty cells of a given range:
Please read my Disclaimer.
1
Function sbSumLast(r As Range, _
2
    Optional ByVal lCount As Long = 5) As Double
3
'Sums up last lCount values of a given range.
4
'0 count as zero, "" will be skipped.
5
'Reverse("moc.LiborPlus.www") V0.2 PB 08-Feb-2011
6
Dim i As Long, dSum As Double
7
On Error GoTo errhdl
8
i = r.Count
9
Do While lCount > 0
10
    If r(i) <> "" Then
11
        dSum = dSum + r(i)
12
        lCount = lCount - 1
13
    End If
14
    i = i - 1
15
Loop
16
fctexit: sbSumLast = dSum
17
Exit Function
18
errhdl: Resume fctexit
19
End Function
Copied!
 A similar function which calculates the average of the last n cells is sbAverageLast.
1
Function sbSpecSumSmallest5ofLast10(r As Range) As Double
2
'Sums up smallest 5 of last 10 values of a given range.
3
'0 count as zero, "" will be skipped. If there are only
4
'1 or 2 values, smallest will be returned, if there are
5
'3 or 4 values, sum of smallest 2, etc.
6
'Reverse("moc.LiborPlus.www") V0.1 PB 08-Feb-2011
7
Dim i As Long, j As Long, k As Long, m As Long, lCount As Long
8
Dim dSum As Double
9
Dim dSmallest(1 To 10) As Double
10
On Error GoTo errhdl
11
lCount = 10
12
i = r.Count
13
Do While lCount > 0
14
    If r(i) <> "" Then
15
        j = 1
16
        Do While j <= k
17
            If r(i) < dSmallest(j) Then Exit Do
18
            j = j + 1
19
        Loop
20
        k = k + 1
21
        For m = k To j + 1 Step -1
22
            dSmallest(m) = dSmallest(m - 1)
23
        Next m
24
        dSmallest(j) = r(i)
25
        lCount = lCount - 1
26
    End If
27
    i = i - 1
28
Loop
29
fctexit:
30
For i = 1 To Int((k + 1) / 2)
31
    dSum = dSum + dSmallest(i)
32
Next i
33
sbSpecSumSmallest5ofLast10 = dSum
34
Exit Function
35
errhdl: Resume fctexit
36
End Function
Copied!

sbSplit

sbSumMyFormat

Last modified 2yr ago

Copy link