Calculate largest value in column year to date?

[Ironman]

Hi

Column A12 to A23357 contains date values.

Column E12 to E23357 are either blank or contain time values in format m:ss, which are formulas Col C/Col D from the same row.

The below is a sample from Oct 1 onwards.

Exercise Log test.xlsm
	A	B	C	D	E
8686	Fri, 1 Oct 2021	OTHER (IB)
8687	Sat, 2 Oct 2021	REST
8688	Sun, 3 Oct 2021	OTHER (IB)
8689	Mon, 4 Oct 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6	2:25:34	13:44
8690	Tue, 5 Oct 2021	OTHER (IB)
8691	Wed, 6 Oct 2021	REST
8692	Thu, 7 Oct 2021	OTHER (IB)
8693	Fri, 8 Oct 2021	Cullingworth Rd/Haworth Rd/Flappit/L Halifax Rd/ Trough Ln/Denholme Rd/ Station Rd (Oxenhope)/ Moorhse Ln/R Marsh Ln (Haworth)/Sun St/ Bridgehouse Ln/Brow Rd Brow Top Rd/Hawrth Rd Turf Ln/Station Road/ Greenside Lane	9.9	2:22:41	14:25
8694	Sat, 9 Oct 2021	OTHER (IB)
8695	Sun, 10 Oct 2021	REST
8696	Mon, 11 Oct 2021	OTHER (IB)
8697	Tue, 12 Oct 2021	Harrogate (Centre - Inn On Cheltenham Parade)/ Valley Park/Pinewoods/ Crag Ln by RHS, Harlow Carr/Nursery Ln/Crag Ln (again)/Otley Road/Back	6.7	1:43:47	15:29
8698	Wed, 13 Oct 2021	OTHER (IB)
8699	Thu, 14 Oct 2021	REST
8700	Fri, 15 Oct 2021	OTHER (IB)
8701	Sat, 16 Oct 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to 5-Rise Locks/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (25/08/2005)	10.4	2:33:41	14:47
Training Log

Cell Formulas
Range	Formula
E8689,E8701,E8697,E8693	E8689	=D8689/C8689

(Although Rows 8710 downwards have yet to be filled, they will eventually be).

What I'd be grateful for is a formula that returns the lowest value in Col E i.e. the fastest pace in the current year to date (for 2022 it will be 2022 year to date).

Many thanks!

 

[Ironman]

P.S. I omitted to ask, if I could please have a second formula with the date of the above achievement that would be great too, thanks again.

 

[Ironman]

I've managed to get the below formula from a similar formula in my workbook, which combines the pace and the relevant date.

=MINIFS(E12:E23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31))&TEXT(MINIFS(A12:A23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31),E12:E23357,MINIFS(E12:E23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31)))," (d mmm)")

Cell A2 contains today's date.

The result is correct and is 0.00892606621773288 (24 Oct)

I don't know how to amend this formula to convert the decimal part of the above to the mm:ss format (it should convert to 12:51). The cell format is mm:ss and I need to do this within the above formula, without a helper cell.

Hope you can help?

Thanks again.

 

[Ironman]

Resolved - many thanks to myself

=(UPPER(TEXT(MINIFS(E12:E23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31)),"mm:ss")&TEXT(MINIFS(A12:A23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31),E12:E23357,MINIFS(E12:E23357,A12:A23357,">="&DATE(YEAR(A2),1,1),A12:A23357,"<="&DATE(YEAR(A2),12,31)))," (d mmm)")))

 

[Peter_SSs]

Glad you got a solution.
With MS357 you could also try this

=LET(a,A12:A23357,e,E12:E23357,rws,YEAR(a)=YEAR(A2),m,MIN(FILTER(e,rws)),TEXT(m,"mm:ss")&UPPER(TEXT(MIN(FILTER(a,rws*(e=m)))," (d mmm)")))

BTW, your formula seems to be doing the opposite of the thread title.

 

[Ironman]

Many thanks Peter - that's really helpful. That function seems like the missing link between formulas and VBA!

 

[Peter_SSs]

You're welcome. The LET function effectively allows you to have variables in your formula. Not only does it allow shorter formulas in many cases but it makes their calculation more efficient as the 'variable' value only needs to be calculated once for the whole formula.

 

[Ironman]

Thank you. I'm actually still awaiting an email address acceptable to MS to be able to take up the subscription but (like others on the forum) the potential of the LET (and Lambda) functions quite excites me, although I suppose it will then involve redoing all the 6 foot long formulas like the one above

 

[Ironman]

Hi Peter, I've just installed MS365 and tried your formula - it returned error "#DIV/0".

Re your PS - yes I see what you mean!

 

[Peter_SSs]

I've just installed MS365 and tried your formula - it returned error "#DIV/0".

That would suggest to me that you one or more #DIV/0! errors in column E (which wouldn't be too surprising given you seem to have division formulas there).
I would suggest that you amend your column E formulas to avoid such errors in that column.
If you do have a simple division like that shown in the mini-sheet above then try something like

=IF(C8689>0,D8689/C8689,"")