Average If - 3 most recent dates

[ExcelRaceRatings]

Hello,

I have a Horse Racing example (please refer to picture) where i would love some advice on how to average a horses rating using criteria based on it's 3 most recent performances.

1) In H4 i would like to obtain an average of the horses rating (Column F) based on it's last 3 most recent starts (Column A) at the NCLE track (Column C)
2) In I4 i would like to do the above except i would like an average of the horses last 3 ratings over 1100m.
3) In J4 an average of the horses rating based on it's 3 most recent starts on a "Soft6" track
4) In K4 like all of the above the same thing expect this time an average of the horses last 3 most recent ratings when carrying 57kg.

I'd be highly grateful if anyone could provide me the most suitable formulas to return the above?

Cheers

 

[FormR]

Hi, next time you post you might want to consider using XL2BB to post your example data rather than post an image. This enables your potential helpers to copy the data into Excel for testing without having to manually re-typing it.

Also, when posting example data, it helps if you show what your expected results are in relation to that example data as this gives your potential helpers a way of checking they are on the right track.

With all that said - perhaps you can try these.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1
2	date	Dist	Track	Weight	Cond	rating		Track	Dist	Cond	Weight
3	26/03/2021	1000m	other	55.5	s7	49.4		NCLE	1100m	s6	57
4	12/02/2022	1000m	other	55.5	s5	41.2		38.133333	39.2	38.35	37.533333
5	14/04/2021	1100m	other	55.5	g3	40.8
6	19/02/2021	1000m	other	55.5	s7	40.4
7	01/01/2022	900m	other	55	g4	40.1
8	07/08/2021	900m	NCLE	59	g4	39.7
9	05/03/2021	1100m	NCLE	55	s5	39
10	22/01/2021	1000m	other	56.5	g4	38.8
11	12/01/2022	1100m	other	56.5	s6	38.8
12	26/02/2022	1100m	other	57	h10	38
13	12/02/2021	1100m	other	56.5	s5	37.8
14	15/01/2021	900m	other	55.5	g4	37.6
15	02/02/2022	1000m	other	57	s6	37.9
16	28/05/2020	1100m	other	57	h8	36.7
17	13/12/2020	900m	other	54.5	g4	36.4
18	12/05/2020	1000m	other	55.5	g4	35.9
19	19/12/2019	1000m	other	56.5	g4	35.7
20	18/12/2021	900m	NCLE	58	g4	35.7
21	29/12/2020	1000m	other	56.5	h5	35.1
Sheet1

Cell Formulas
Range	Formula
H4	H4	=AVERAGEIFS($F$3:$F$21,$A$3:$A$21,">="&AGGREGATE(14,6,$A$3:$A$21/($C$3:$C$21=H3),MIN(3,COUNTIFS($C$3:$C$21,H3))),$C$3:$C$21,H3)
I4	I4	=AVERAGEIFS($F$3:$F$21,$A$3:$A$21,">="&AGGREGATE(14,6,$A$3:$A$21/($B$3:$B$21=I3),MIN(3,COUNTIFS($B$3:$B$21,I3))),$B$3:$B$21,I3)
J4	J4	=AVERAGEIFS($F$3:$F$21,$A$3:$A$21,">="&AGGREGATE(14,6,$A$3:$A$21/($E$3:$E$21=J3),MIN(3,COUNTIFS($E$3:$E$21,J3))),$E$3:$E$21,J3)
K4	K4	=AVERAGEIFS($F$3:$F$21,$A$3:$A$21,">="&AGGREGATE(14,6,$A$3:$A$21/($D$3:$D$21=K3),MIN(3,COUNTIFS($D$3:$D$21,K3))),$D$3:$D$21,K3)