Changing an array formula to a LET

[Marvo]

Hi All, having just upgraded to excel 2019 to excel 2021, I now have use of the improved formula tools. I have recently changed all the array formula in the grid looking for Consecutives results by using the LET function.

For example I replace this array

=MAX(FREQUENCY(IF((ALL!$J$2:ALL!$J$6000="W")*(ALL!$I$2:ALL!$I$6000="League")*(ALL!$E$2:ALL!$E$6000="H"),ROW(ALL!$J$2:ALL!$J$6000)),IF((ALL!$J$2:ALL!$J$6000<>"W")*(ALL!$I$2:ALL!$I$6000="League")*(ALL!$E$2:ALL!$E$6000="H"),ROW(ALL!$J$2:ALL!$J$6000))))

with this

=LET(t,FILTER(ALL!$K$2:$K$9999,(ALL!$J$2:$J$9999="League")*(ALL!$E$2:$E$9999="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="W",s),IF(t<>"W",s))))

I've now moved on to the Longest Runs but can't fathom how to get a start.

For example, in cell C16, the question being addressed is what is the longest run of games the team has gone without a defeat, with the criteria being games played at home and in the League (Not cup).

This array formula works fine

=MAX(FREQUENCY(IF((ALL!$K$2:$K$6000<>"L")*(ALL!$K$2:$K$6000<>"")*(ALL!$J$2:$J$6000="League")*(ALL!$E$2:$E$6000="H"),ROW(ALL!$K$2:$K$6000)),IF((ALL!$K$2:$K$6000="L")*(ALL!$J$2:$J$6000="League")*(ALL!$E$2:$E$6000="H"),ROW(ALL!$K$2:$K$6000))))

but I would very much like to take advantage of the simpler (and shorter) LET function if possible.

If anybody could point me in the right direction I'd be most grateful.

 

[ExcelToDAX]

how about:

=LET(ColumnK,ALL!$K$2:$K$6000,ColumnJ,ALL!$J$2:$J$6000,ColumnE,ALL!$E$2:$E$6000,MAX(FREQUENCY(IF((ColumnK<>"L")*(ColumnK<>"")*(ColumnJ="League")*(ColumnE="H"),ROW(ColumnK)),IF((ColumnK="L")*(ColumnJ="League")*(ColumnE="H"),ROW(ColumnK)))))

 

[Fluff]

How about

=LET(t,FILTER(ALL!$K$2:$K$9999,(ALL!$K$2:$K$9999<>"")*(ALL!$J$2:$J$9999="League")*(ALL!$E$2:$E$9999="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"L",s),IF(t="L",s))))

 

[Marvo]

That produces the correct answer. Hopefully I'll be able to adapt it to the other boxes. Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.