Averaging last 3 numbers in a column that match criteria from another column??

[apwaterski]

How do I average the last 3 numbers in a column, that match a word in another column. For example...
Column A1:A10 = green, green, red, red, green, blue, green, green, red, green.
Column B1:B10= 1,9,7,3,8,7,6,3,1,9.
I want to average the last 3 numbers in column B, that are green. The answer is 6 (the average of 9,3,6). I will be adding to both column A and B daily, and I want the formula to always find the average of the LAST 3 numbers in column B that are green.
Thanks for the help!

 

[Michael M]

I'm guessing there will be a formula solution, but in the meantime will a VBA do !!
This will put the answer in Cell C1
and will work regardless of the length o Columns A & B

Sub MM1()
Dim lr As Long, r As Long, n As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = 0
x = 0
For r = lr To 1 Step -1
    If Range("A" & r).Value = "green" Then
        n = n + Range("B" & r).Value
        x = x + 1
    If x = 3 Then Range("C1").Value = n / 3
    End If
Next r
End Sub

 

[Aladin Akyurek]

Let's define color in Formulas | Name Manager as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

and value as:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$A:$A))

Note. Adjust the sheet name to suit.

Row\Col	A​	B​	C​	D​
1​	green	1​		green
2​	green	9​		6​
3​	red	7​
4​	red	3​
5​	green	8​
6​	blue	7​
7​	green	6​
8​	green	3​
9​	red	1​
10​	green	9​

In D2 control+shift+enter, not just enter:

=AVERAGE(IF(ROW(color)>=LARGE(IF(color=D1,ROW(color)),MIN(3,COUNTIFS(color,D1))),IF(color=D1,value)))

 

[apwaterski]

Thanks for the help! That's exactly what I'm looking for. The formula does not work on my tablet. Do you know of any way to make an array formula work on excel mobile?

 

[Aladin Akyurek]

Thanks for the help! That's exactly what I'm looking for.

You are welcome.

The formula does not work on my tablet. Do you know of any way to make an array formula work on excel mobile?

I'm not familiar with "excel mobile". Would the following work?

=SUMPRODUCT(--(ROW(color)>=LARGE(INDEX((color=D1)*ROW(color),0),
    MIN(3,COUNTIFS(color,D1)))),(color=D1)*value)/MIN(3,COUNTIFS(color,D1))

 

[apwaterski]

I'm not familiar with "excel mobile". Would the following work?

=SUMPRODUCT(--(ROW(color)>=LARGE(INDEX((color=D1)*ROW(color),0),
    MIN(3,COUNTIFS(color,D1)))),(color=D1)*value)/MIN(3,COUNTIFS(color,D1))

[/QUOTE]

That's incredible! This formula works on excel mobile. Thanks so much!

 

[Aladin Akyurek]

I'm not familiar with "excel mobile". Would the following work?

=SUMPRODUCT(--(ROW(color)>=LARGE(INDEX((color=D1)*ROW(color),0),
    MIN(3,COUNTIFS(color,D1)))),(color=D1)*value)/MIN(3,COUNTIFS(color,D1))

That's incredible! This formula works on excel mobile. Thanks so much!

This is great. Thanks for the update.

 

[apwaterski]

How do you SUM the last 3 instead of averaging??

 

[Aladin Akyurek]

How do you SUM the last 3 instead of averaging??

It would be:

=SUMPRODUCT(--(ROW(color)>=LARGE(INDEX((color=D1)*ROW(color),0), MIN(3,COUNTIFS(color,D1)))),(color=D1)*value)

on your Excel mobile.

 

[apwaterski]

Thanks again!