Average a 5 most recent Values

Question

Hi,

I would like to average the 5 most recent values within a category. For example, average value of Fish for the 5 most recent dates.

Sample of the data below. I am using Excel from Office Professional Plus 2010.

How can I do this?

Regards,

Azz

Catagorie	Date	Value
Fish	8/12/2015	6
Fish	2/06/2015	8
Fish	8/02/2015	6
Snow	13/04/2015	3
Snow	7/05/2015	7
Bird	11/10/2015	9
Crab	3/07/2015	2
Dog	22/09/2015	9
Cat	11/08/2015	1
Dog	28/10/2015	5
Crab	17/08/2015	8
Bird	27/11/2015	4
Bird	18/01/2015	1
Dog	26/02/2015	9
Cat	21/11/2015	7
Snow	25/11/2015	10
Crab	23/03/2015	2
Crab	5/12/2015	7
Cat	15/04/2015	2
Fish	20/07/2015	5
Fish	12/10/2015	3
Dog	12/11/2015	5
Crab	4/12/2015	6
Bird	14/03/2015	5
Bird	3/05/2015	9
Fish	16/02/2015	4
Snow	6/01/2015	10
Cat	14/03/2015	1
Crab	29/03/2015	5
Fish	26/05/2015	9
Dog	14/05/2015	4
Cat	20/04/2015	10

Answer 1

Let's say your data are in A1:C33.

Enter the category you want to analyze (e.g. Fish) in E2.

Enter the following array formula in F2, confirmed with Ctrl+Shift+Enter:

=AVERAGE(IF((A2:A33=E2)*(B2:B33>=LARGE(IF(A2:A33=E2,B2:B33),MIN(5,COUNTIF(A2:A33,E2)))),C2:C33)

Remember, each time you edit the formula, you will have to confirm it with Ctrl+Shift+Enter again, otherwise it will not return the correct value.

Answer 2

@Azz658,

Alternative with a non-array formula***** (Enter only) with data in A2:C33:

in F2:

=IF(COUNTIF($A$2:$A$33,E2)=0, "Not found", SUMPRODUCT(AGGREGATE(14,6,(1/((($A$2:$A$33=E2)*($B$2:$B$33))>=LARGE(($A$2:$A$33=E2)*($B$2:$B$33),MIN(5,COUNTIF($A$2:$A$33,E2))))*$C$2:$C$33),ROW(INDIRECT("1:"&MIN(5,COUNTIF($A$2:$A$33,E2)))))) / MIN(5,COUNTIF($A$2:$A$33,E2)))

and copy down

* requires Excel >/= 2010 (AGGREGATE function)

Answer 3

Hi,

Is this the result you are expecting?

Answer 4

Thanks Lz,

I tried changing the formula to be an average of the most recent 10 values by changing all the Min(5.... to 10. This did not seem to work. Have I missed something?

Answer 5

Thanks HansV, works great