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.
Function sbAverageLast(s As String, _
rName As Range, rPoint As Range, n As Long) As Variant
'Returns average of last n cells in rPoint where s = rName.
'Error value if s does not exist in rName n or more times.
'Reverse("moc.LiborPlus.www") PB 30-Jan-2011 V0.21
Dim r1 As Range, r2 As Range
Dim i As Long, j As Long
Dim d As Double
Set r1 = Intersect(rName, rName.Parent.UsedRange)
Set r2 = Intersect(rPoint, rPoint.Parent.UsedRange)
j = 0
d = 0#
For i = r1.Count To 1 Step -1
If r1(i) = s Then
j = j + 1
d = d + r2(i)
If j >= n Then
sbAverageLast = d / j
Exit Function
End If
End If
Next i
Set r1 = Nothing
Set r2 = Nothing
sbAverageLast = CVErr(xlErrValue)
End Function
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.
Copy link