Max Value in the previous 365 days

[pilot330]

Hi
I'm looking for a formula for Column F (in this case F23)
I need this formula to return the highest Rating in COL E, in the previous 365 days in COL A, for each individual horse in COL C.
In the example F23 returns 96 as that is the highest value for horse A LADIES MILAN in the previous 365 days.
Grateful for any help, stumped on this at the moment.
Thanks,
Paul

Book6
	A	B	C	D	E	F
1	Date	Time	Horse	Course	Rating	Max Rating
2	26/10/2019	17:30	A Book Of Intrigue	Cheltenham	0
3	28/11/2019	12:25	A Book Of Intrigue	Ffos Las	91
4	19/12/2019	13:40	A Book Of Intrigue	Hereford	87
5	31/12/2019	12:45	A Book Of Intrigue	Uttoxeter	0
6	26/10/2019	14:40	A Different World	Galway	90
7	09/11/2019	12:00	A Different World	Naas	49
8	15/12/2019	11:30	A Different World	Navan	93
9	10/10/2019	16:15	A Few Bob Short	Thurles	68
10	28/10/2019	16:05	A Few Bob Short	Galway	71
11	07/10/2019	16:35	A Girl Like Me	Tipperary	0
12	16/10/2019	16:50	A Girl Like Me	Punchestown	77
13	03/10/2019	17:10	A Go Go	Wolverhampton	53
14	18/10/2019	17:25	A Go Go	Wolverhampton	44
15	09/12/2019	16:45	A Go Go	Newcastle	56
16	17/11/2019	13:40	A Great View	Punchestown	138
17	28/12/2019	12:50	A Great View	Leopardstown	129
18	22/10/2019	17:30	A Hundred Echoes	Kempton	60
19	06/10/2019	14:50	A Ladies Milan	Kelso	65
20	26/10/2019	13:15	A Ladies Milan	Kelso	76
21	08/11/2019	13:00	A Ladies Milan	Hexham	0
22	16/11/2019	12:30	A Ladies Milan	Wetherby	96
23	08/12/2019	15:15	A Ladies Milan	Kelso	19	96
24	07/12/2019	15:40	A Large One Please	Chepstow	86
25	09/10/2019	15:05	A Likely Story	Navan	33
26	13/10/2019	17:35	A Likely Story	Curragh	28
27	08/11/2019	18:15	A Likely Story	Dundalk	47
28	13/12/2019	20:45	A Likely Story	Dundalk	57
29	18/12/2019	18:30	A Likely Story	Dundalk	54
30	13/10/2019	14:40	A New Dawn	Curragh	94
31	17/10/2019	16:00	A New Siege	Carlisle	62
32	12/12/2019	13:00	A New Siege	Taunton	94
33	01/11/2019	13:30	A Perfect Gift	Uttoxeter	83
34	31/10/2019	17:10	A Place To Dream	Kempton	66
35	30/11/2019	12:30	A Place To Dream	Lingfield	61
36	16/12/2019	19:40	A Place To Dream	Wolverhampton	48
37	10/11/2019	14:10	A Plus Tard	Navan	152
38	27/12/2019	13:10	A Plus Tard	Leopardstown	164
39	25/11/2019	15:25	A Star Above	Chelmsford City	59
40	28/12/2019	14:45	A Star Above	Lingfield	69
Sheet1

 

[DRSteele]

I think this will work.

F2: =MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,">="&(TODAY()-365))

 

[pilot330]

Thanks. The formula is returning the highest overall rating for that horse. I need the highest rating for the previous 365 days only.

Book6
	A	B	C	D	E	F
1	Date	Time	Horse	Course	Rating	Max Rating
2	26/10/2019	17:30	A Book Of Intrigue	Cheltenham	0	91
3	28/11/2019	12:25	A Book Of Intrigue	Ffos Las	91	91
4	19/12/2019	13:40	A Book Of Intrigue	Hereford	87	91
5	31/12/2019	12:45	A Book Of Intrigue	Uttoxeter	0	91
6	26/10/2019	14:40	A Different World	Galway	90	93
7	09/11/2019	12:00	A Different World	Naas	49	93
8	15/12/2019	11:30	A Different World	Navan	93	93
9	10/10/2019	16:15	A Few Bob Short	Thurles	68	71
10	28/10/2019	16:05	A Few Bob Short	Galway	71	71
11	07/10/2019	16:35	A Girl Like Me	Tipperary	0	77
12	16/10/2019	16:50	A Girl Like Me	Punchestown	77	77
13	03/10/2019	17:10	A Go Go	Wolverhampton	53	56
14	18/10/2019	17:25	A Go Go	Wolverhampton	44	56
15	09/12/2019	16:45	A Go Go	Newcastle	56	56
16	17/11/2019	13:40	A Great View	Punchestown	138	138
17	28/12/2019	12:50	A Great View	Leopardstown	129	138
18	22/10/2019	17:30	A Hundred Echoes	Kempton	60	60
19	06/10/2019	14:50	A Ladies Milan	Kelso	65	96
20	26/10/2019	13:15	A Ladies Milan	Kelso	76	96
21	08/11/2019	13:00	A Ladies Milan	Hexham	0	96
22	16/11/2019	12:30	A Ladies Milan	Wetherby	96	96
23	08/12/2019	15:15	A Ladies Milan	Kelso	19	96
24	07/12/2019	15:40	A Large One Please	Chepstow	86
25	09/10/2019	15:05	A Likely Story	Navan	33
26	13/10/2019	17:35	A Likely Story	Curragh	28
27	08/11/2019	18:15	A Likely Story	Dundalk	47
28	13/12/2019	20:45	A Likely Story	Dundalk	57
29	18/12/2019	18:30	A Likely Story	Dundalk	54
30	13/10/2019	14:40	A New Dawn	Curragh	94
31	17/10/2019	16:00	A New Siege	Carlisle	62
32	12/12/2019	13:00	A New Siege	Taunton	94
33	01/11/2019	13:30	A Perfect Gift	Uttoxeter	83
34	31/10/2019	17:10	A Place To Dream	Kempton	66
35	30/11/2019	12:30	A Place To Dream	Lingfield	61
36	16/12/2019	19:40	A Place To Dream	Wolverhampton	48
37	10/11/2019	14:10	A Plus Tard	Navan	152
38	27/12/2019	13:10	A Plus Tard	Leopardstown	164
39	25/11/2019	15:25	A Star Above	Chelmsford City	59
40	28/12/2019	14:45	A Star Above	Lingfield	69
Sheet1

Cell Formulas
Range	Formula
F2:F23	F2	=MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,">="&(TODAY()-365))

 

[DRSteele]

All your dates are within 365 days of today.

 

[pilot330]

I need the formula to look up the best rating within the previous 365 days, not including today.
There is an example of the numbers I need returned for COL F in the adjacent column. Thanks.

Book6
	A	B	C	D	E	F	G
1	Date	Time	Horse	Course	Rating	Max Rating
2	26/10/2019	17:30	A Book Of Intrigue	Cheltenham	0	91
3	28/11/2019	12:25	A Book Of Intrigue	Ffos Las	91	91
4	19/12/2019	13:40	A Book Of Intrigue	Hereford	87	91
5	31/12/2019	12:45	A Book Of Intrigue	Uttoxeter	0	91
6	26/10/2019	14:40	A Different World	Galway	90	93
7	09/11/2019	12:00	A Different World	Naas	49	93
8	15/12/2019	11:30	A Different World	Navan	93	93
9	10/10/2019	16:15	A Few Bob Short	Thurles	68	71
10	28/10/2019	16:05	A Few Bob Short	Galway	71	71
11	07/10/2019	16:35	A Girl Like Me	Tipperary	0	77
12	16/10/2019	16:50	A Girl Like Me	Punchestown	77	77
13	03/10/2019	17:10	A Go Go	Wolverhampton	53	56
14	18/10/2019	17:25	A Go Go	Wolverhampton	44	56
15	09/12/2019	16:45	A Go Go	Newcastle	56	56
16	17/11/2019	13:40	A Great View	Punchestown	138	138
17	28/12/2019	12:50	A Great View	Leopardstown	129	138
18	22/10/2019	17:30	A Hundred Echoes	Kempton	60	60
19	06/10/2019	14:50	A Ladies Milan	Kelso	65	96	0
20	26/10/2019	13:15	A Ladies Milan	Kelso	76	96	65
21	08/11/2019	13:00	A Ladies Milan	Hexham	0	96	76
22	16/11/2019	12:30	A Ladies Milan	Wetherby	96	96	76
23	08/12/2019	15:15	A Ladies Milan	Kelso	19	96	96
24	07/12/2019	15:40	A Large One Please	Chepstow	86
25	09/10/2019	15:05	A Likely Story	Navan	33
26	13/10/2019	17:35	A Likely Story	Curragh	28
27	08/11/2019	18:15	A Likely Story	Dundalk	47
28	13/12/2019	20:45	A Likely Story	Dundalk	57
29	18/12/2019	18:30	A Likely Story	Dundalk	54
30	13/10/2019	14:40	A New Dawn	Curragh	94
31	17/10/2019	16:00	A New Siege	Carlisle	62
32	12/12/2019	13:00	A New Siege	Taunton	94
33	01/11/2019	13:30	A Perfect Gift	Uttoxeter	83
34	31/10/2019	17:10	A Place To Dream	Kempton	66
35	30/11/2019	12:30	A Place To Dream	Lingfield	61
36	16/12/2019	19:40	A Place To Dream	Wolverhampton	48
37	10/11/2019	14:10	A Plus Tard	Navan	152
38	27/12/2019	13:10	A Plus Tard	Leopardstown	164
39	25/11/2019	15:25	A Star Above	Chelmsford City	59
40	28/12/2019	14:45	A Star Above	Lingfield	69
Sheet1

Cell Formulas
Range	Formula
F2:F23	F2	=MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,">="&(TODAY()-365))

 

[DRSteele]

Do you mean the PRIOR 365 days for each horse for each row? Why didn't you say that?

 

[pilot330]

Yep. I did mention "the previous 365 days" in my opening post. Thanks.

 

[DRSteele]

Previous means today.

Anyway, the values you put in ColG don't make sense. G19 should be 65 G20 should be 76 and G22 should be 96.

 

[pilot330]

G19 is 0 because there were no runs for A LADIES MILAN prior to the 06/10/2019

Anybody else got a solution please...

 

[DRSteele]

Try this:

F2: =MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,"<"&A2,$A$2:$A$40,">"&(A2-365))