CSE update to Dynamic Array

[dc8]

Been retired for some time now and only used an old copy of Office 2013 at home for years.
I now have a shiny new copy of Office 2021 and a spreadsheet I use daily to monitor my bank account was playing up and not calculating correctly
when opened in 2021.
Managed after some time to discover that over time some "features" had crept into a CSE formula in the sheet. I managed to fix this
by recreating the whole formula again and using ctrl-shift-enter
However, I am not sure that this formula
=IF(MIN(IF(YEAR(A2)&MONTH(A2)=YEAR($A$2:$A$6548)&MONTH($A$2:$A$6548),$F$2:$F$6548,""))=F2,"<<<<<<<<<<","")
is as efficient as it could be bearing in mind the developments that have taken place in Excel in the years since I used it regularly.
Would anyone be kind enough to suggest a more up-to-date version of this formula which just flags the lowest amount available at any
point in each month.
Thanks in advance for any pointers.
Chris S

 

[RoryA]

Welcome to the forum.

You could use MINIFS:

=IF(MINIFS($F$2:$F$6548,$A$2:$A$6548,">="&EOMONTH(A2,-1)+1,$A$2:$A$6548,"<"&EOMONTH(A2,0)+1)=F2,"<<<<<<<<<<","")

 

[dc8]

Welcome to the forum.

You could use MINIFS:

=IF(MINIFS($F$2:$F$6548,$A$2:$A$6548,">="&EOMONTH(A2,-1)+1,$A$2:$A$6548,"<"&EOMONTH(A2,0)+1)=F2,"<<<<<<<<<<","")

Thanks SO much Rory !!
The spreadsheet now calculates blindingly fast and works far better than the old version.
I bet there are a zillion new features in the later versions, if I get time I'll have a play with some other formulas and see if I can work out how to tweak them.
ATB,
Chris S