Sulprobil
Search…
sbAverageLast
If you have a list of names in column A and corresponding values in column B and you want to show the average of the last 3, 6, or 9 values for some (or all) names - if there are at least 3, 6, or 9 such names. You can solve this a) with a user-defined function b) via worksheet functions
Please notice that the data table in columns A and B of this example is going beyond row 10.
Please read my Disclaimer.
1
Function sbAverageLast(s As String, _
2
rName As Range, rPoint As Range, n As Long) As Variant
3
'Returns average of last n cells in rPoint where s = rName.
4
'Error value if s does not exist in rName n or more times.
5
'Reverse("moc.LiborPlus.www") PB 30-Jan-2011 V0.21
6
Dim r1 As Range, r2 As Range
7
Dim i As Long, j As Long
8
Dim d As Double
9
10
Set r1 = Intersect(rName, rName.Parent.UsedRange)
11
Set r2 = Intersect(rPoint, rPoint.Parent.UsedRange)
12
j = 0
13
d = 0#
14
For i = r1.Count To 1 Step -1
15
If r1(i) = s Then
16
j = j + 1
17
d = d + r2(i)
18
If j >= n Then
19
sbAverageLast = d / j
20
Exit Function
21
End If
22
End If
23
Next i
24
Set r1 = Nothing
25
Set r2 = Nothing
26
sbAverageLast = CVErr(xlErrValue)
27
End Function
Copied!
Biff (T. Valko) came up with the following worksheet function solution. Please notice that you have to enter this formula as an array formula. You need to enter it with CTRL + SHIFT + ENTER, not only with ENTER:
In cell D7: =IF(COUNTIF($A$2:$A$99,$C7)<D$6,"",AVERAGE(IF(ROW($A$2:$A$99)>=LARGE(IF($A$2:$A$99=$C7, ROW($A$2:$A$99)),D$6),IF($A$2:$A$99=$C7,$B$2:$B$99))))
Despite the fact that Biff's formula is remarkably faster I suggest to prefer the macro function approach. The function code is encapsulated into one location only, and the name reference and the number of values for the average only need to be given once - which is less error prone.
A different but similar function is sbSumLast which sums up the last specified non-empty cells.
Last modified 1yr ago
Copy link