Sulprobil
Search…
⌃K

# sbSumLast

Here is a VBA function which sums the last non-empty cells of a given range:
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