Average Of Last 3 Values Meeting Criteria

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

To attach a sample workbook.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

It looks like the dates are in ascending order so would it be safe to assume we can average the 3 bottom-most numbers for the criteria?

Will there always be at least 3 instances of the criteria? If not, then what should happen?

Enter formula in cell E2 and copy down

Formula:

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

v	A	B	C	D	E
1	Date	Animal	Value	Unique Names	Avg of last 3
2	1/1/2011	Dog	17	Dog	8.67
3	2/3/2011	Cat	10	Cat	13.00
4	8/7/2011	Cat	7	Bird	8.33
5	8/9/2011	Dog	8
6	9/1/2012	Cat	13
7	8/7/2012	Bird	9
8	6/8/2012	Cat	2
9	4/7/2013	Bird	3
10	8/7/2013	Dog	20
11	5/7/2014	Bird	15
12	5/8/2014	Cat	19
13	6/8/2014	Cat	16
14	8/9/2014	Bird	12
15	3/5/2015	Dog	11
16	4/7/2015	Dog	5
17	6/7/2015	Bird	18
18	5/9/2015	Bird	1
19	6/6/2016	Dog	14
20	6/8/2016	Bird	6
21	7/8/2016	Cat	4
22	7/8/2016	Dog	7

Originally Posted by alkey

=average(index(c:c,n(if(1,large(index((b$2:b$22=d2)*(row(b$2:b$22)),0),row(indirect("1:3")))))))
***array formula

This looks better
=AVERAGE(INDEX(C:C,N(IF(1,LARGE(INDEX((B$2:B$22=D2)*(ROW(B$2:B$22)),0),{1,2,3})))))

If the data was in random order...

E2 = some animal

Array entered**:

=AVERAGE(IF(A2:A22=LARGE(IF(B2:B22=E2,A2:A22),{1,2,3}),IF(B2:B22=E2,C2:C22)))

This array formula** entered in

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.