Sulprobil
Search…
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!
Last modified 1yr ago
Copy link