Average last 5 exact matches if Condition is met in other column

[cipirica]

Hello everybody. I need help badly from an excel expert. I need a vba to average the last 5 matches in a column. Example:
A. B. C
1 BILL. 9
2 ANDY. 7
3 BILL. 4
4 ANDY. 0
5 BILL. 6
6 ANDY. 2
7 ANDY. 10
8 BILL. 5
9 BILL. 7
10ANDY
11BILL
So, in column A a have names and in B numbers. Starting with row 10 for example, in column C 10 I want the average of the previous five Andy from column B, in C11 the average of previous 5 Bill from column B and so on until the last available cell in column A. I do not need a formula becouse I have 100.000 rows and a formula to give me what I want it will take for ever. I think a vba would be faster. Thank you in advance and I hope someone will spend some of his/hers precious time to help me.

 

[kvsrinivasamurthy]

Data=A2:C13
Cri=ANDY.
Average=Res

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY."
A = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
For T = UBound(A, 1) To 1 Step -1
If A(T, 2) = Cri Then: X = X + 1: B(X) = A(T, 3)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
End Sub

 

[cipirica]

Data=A2:C13
Cri=ANDY.
Average=Res

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY."
A = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
For T = UBound(A, 1) To 1 Step -1
If A(T, 2) = Cri Then: X = X + 1: B(X) = A(T, 3)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
End Sub

Thx a lot. When I get home I will give it a try. I will let you know how it works

 

[cipirica]

Data=A2:C13
Cri=ANDY.
Average=Res

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY."
A = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
For T = UBound(A, 1) To 1 Step -1
If A(T, 2) = Cri Then: X = X + 1: B(X) = A(T, 3)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
End Sub

Hi, kvsrinivasamurthy. THX for your time. On this line Res = WorksheetFunction.Average(B) i get this error
"Excel error 1004 "Unable to get .... property of WorksheetFunction class"
and i can not fix it. If you can help me i would be gratefull.

 

[kvsrinivasamurthy]

Upload image of data.

 

[cipirica]

Upload image of data.

 

[kvsrinivasamurthy]

As perbyour data

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY."
A = Range(Range("A4"), Range("A4").End(xlDown).End(xlToRight))
For T = UBound(A, 1) To 1 Step -1
If A(T, 1) = Cri Then: X = X + 1: B(X) = A(T, 2)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
[F2] = Res
End Sub

 

[cipirica]

As perbyour data

Sub GetAverage()
Dim A, Res
Dim T As Long, X As Long
Dim Cri As String
Dim B(1 To 5)

Cri = "ANDY."
A = Range(Range("A4"), Range("A4").End(xlDown).End(xlToRight))
For T = UBound(A, 1) To 1 Step -1
If A(T, 1) = Cri Then: X = X + 1: B(X) = A(T, 2)
If X = 5 Then Exit For
Next T
Res = WorksheetFunction.Average(B)
[F2] = Res
End Sub

It is not my day today.

 

[gargamalebarbosa]

Hi, if you prefer formula.. İt is ARRAY formula , exit from formula with CTRL+SHIFT+ENTER

 

[cipirica]

Hi, if you prefer formula.. İt is ARRAY formula , exit from formula with CTRL+SHIFT+ENTER

View attachment 72014

Sorry. I get the #name? error