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.

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.