Max Value in the previous 365 days

pilot330

New Member
Joined
Feb 19, 2004
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DateTimeHorseCourseRatingMax Rating
226/10/201917:30A Book Of IntrigueCheltenham0
328/11/201912:25A Book Of IntrigueFfos Las91
419/12/201913:40A Book Of IntrigueHereford87
531/12/201912:45A Book Of IntrigueUttoxeter0
626/10/201914:40A Different WorldGalway90
709/11/201912:00A Different WorldNaas49
815/12/201911:30A Different WorldNavan93
910/10/201916:15A Few Bob ShortThurles68
1028/10/201916:05A Few Bob ShortGalway71
1107/10/201916:35A Girl Like MeTipperary0
1216/10/201916:50A Girl Like MePunchestown77
1303/10/201917:10A Go GoWolverhampton53
1418/10/201917:25A Go GoWolverhampton44
1509/12/201916:45A Go GoNewcastle56
1617/11/201913:40A Great ViewPunchestown138
1728/12/201912:50A Great ViewLeopardstown129
1822/10/201917:30A Hundred EchoesKempton60
1906/10/201914:50A Ladies MilanKelso65
2026/10/201913:15A Ladies MilanKelso76
2108/11/201913:00A Ladies MilanHexham0
2216/11/201912:30A Ladies MilanWetherby96
2308/12/201915:15A Ladies MilanKelso1996
2407/12/201915:40A Large One PleaseChepstow86
2509/10/201915:05A Likely StoryNavan33
2613/10/201917:35A Likely StoryCurragh28
2708/11/201918:15A Likely StoryDundalk47
2813/12/201920:45A Likely StoryDundalk57
2918/12/201918:30A Likely StoryDundalk54
3013/10/201914:40A New DawnCurragh94
3117/10/201916:00A New SiegeCarlisle62
3212/12/201913:00A New SiegeTaunton94
3301/11/201913:30A Perfect GiftUttoxeter83
3431/10/201917:10A Place To DreamKempton66
3530/11/201912:30A Place To DreamLingfield61
3616/12/201919:40A Place To DreamWolverhampton48
3710/11/201914:10A Plus TardNavan152
3827/12/201913:10A Plus TardLeopardstown164
3925/11/201915:25A Star AboveChelmsford City59
4028/12/201914:45A Star AboveLingfield69
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
I think this will work.

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

pilot330

New Member
Joined
Feb 19, 2004
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Thanks. The formula is returning the highest overall rating for that horse. I need the highest rating for the previous 365 days only.

Book6
ABCDEF
1DateTimeHorseCourseRatingMax Rating
226/10/201917:30A Book Of IntrigueCheltenham091
328/11/201912:25A Book Of IntrigueFfos Las9191
419/12/201913:40A Book Of IntrigueHereford8791
531/12/201912:45A Book Of IntrigueUttoxeter091
626/10/201914:40A Different WorldGalway9093
709/11/201912:00A Different WorldNaas4993
815/12/201911:30A Different WorldNavan9393
910/10/201916:15A Few Bob ShortThurles6871
1028/10/201916:05A Few Bob ShortGalway7171
1107/10/201916:35A Girl Like MeTipperary077
1216/10/201916:50A Girl Like MePunchestown7777
1303/10/201917:10A Go GoWolverhampton5356
1418/10/201917:25A Go GoWolverhampton4456
1509/12/201916:45A Go GoNewcastle5656
1617/11/201913:40A Great ViewPunchestown138138
1728/12/201912:50A Great ViewLeopardstown129138
1822/10/201917:30A Hundred EchoesKempton6060
1906/10/201914:50A Ladies MilanKelso6596
2026/10/201913:15A Ladies MilanKelso7696
2108/11/201913:00A Ladies MilanHexham096
2216/11/201912:30A Ladies MilanWetherby9696
2308/12/201915:15A Ladies MilanKelso1996
2407/12/201915:40A Large One PleaseChepstow86
2509/10/201915:05A Likely StoryNavan33
2613/10/201917:35A Likely StoryCurragh28
2708/11/201918:15A Likely StoryDundalk47
2813/12/201920:45A Likely StoryDundalk57
2918/12/201918:30A Likely StoryDundalk54
3013/10/201914:40A New DawnCurragh94
3117/10/201916:00A New SiegeCarlisle62
3212/12/201913:00A New SiegeTaunton94
3301/11/201913:30A Perfect GiftUttoxeter83
3431/10/201917:10A Place To DreamKempton66
3530/11/201912:30A Place To DreamLingfield61
3616/12/201919:40A Place To DreamWolverhampton48
3710/11/201914:10A Plus TardNavan152
3827/12/201913:10A Plus TardLeopardstown164
3925/11/201915:25A Star AboveChelmsford City59
4028/12/201914:45A Star AboveLingfield69
Sheet1
Cell Formulas
RangeFormula
F2:F23F2=MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,">="&(TODAY()-365))
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
All your dates are within 365 days of today.
 

pilot330

New Member
Joined
Feb 19, 2004
Messages
48
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFG
1DateTimeHorseCourseRatingMax Rating
226/10/201917:30A Book Of IntrigueCheltenham091
328/11/201912:25A Book Of IntrigueFfos Las9191
419/12/201913:40A Book Of IntrigueHereford8791
531/12/201912:45A Book Of IntrigueUttoxeter091
626/10/201914:40A Different WorldGalway9093
709/11/201912:00A Different WorldNaas4993
815/12/201911:30A Different WorldNavan9393
910/10/201916:15A Few Bob ShortThurles6871
1028/10/201916:05A Few Bob ShortGalway7171
1107/10/201916:35A Girl Like MeTipperary077
1216/10/201916:50A Girl Like MePunchestown7777
1303/10/201917:10A Go GoWolverhampton5356
1418/10/201917:25A Go GoWolverhampton4456
1509/12/201916:45A Go GoNewcastle5656
1617/11/201913:40A Great ViewPunchestown138138
1728/12/201912:50A Great ViewLeopardstown129138
1822/10/201917:30A Hundred EchoesKempton6060
1906/10/201914:50A Ladies MilanKelso65960
2026/10/201913:15A Ladies MilanKelso769665
2108/11/201913:00A Ladies MilanHexham09676
2216/11/201912:30A Ladies MilanWetherby969676
2308/12/201915:15A Ladies MilanKelso199696
2407/12/201915:40A Large One PleaseChepstow86
2509/10/201915:05A Likely StoryNavan33
2613/10/201917:35A Likely StoryCurragh28
2708/11/201918:15A Likely StoryDundalk47
2813/12/201920:45A Likely StoryDundalk57
2918/12/201918:30A Likely StoryDundalk54
3013/10/201914:40A New DawnCurragh94
3117/10/201916:00A New SiegeCarlisle62
3212/12/201913:00A New SiegeTaunton94
3301/11/201913:30A Perfect GiftUttoxeter83
3431/10/201917:10A Place To DreamKempton66
3530/11/201912:30A Place To DreamLingfield61
3616/12/201919:40A Place To DreamWolverhampton48
3710/11/201914:10A Plus TardNavan152
3827/12/201913:10A Plus TardLeopardstown164
3925/11/201915:25A Star AboveChelmsford City59
4028/12/201914:45A Star AboveLingfield69
Sheet1
Cell Formulas
RangeFormula
F2:F23F2=MAXIFS($E$2:$E$40,$C$2:$C$40,C2,$A$2:$A$40,">="&(TODAY()-365))
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
Do you mean the PRIOR 365 days for each horse for each row? Why didn't you say that?
 

pilot330

New Member
Joined
Feb 19, 2004
Messages
48
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 19, 2004
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
Try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top