Winning Streak

[honkin]

I have a sheet which keeps records of football results. I'd like to be able to keep a tally of longest losing streak and longest losing streak.

Column K has the results, either WON or LOST, so I tried to use the SCAN function along with MAX to get the desired result

Here is the formula

=MAX(SCAN(0,K5:K100000,LAMBDA(a,v,IF(v="WON",a+1,0))))

It returns #NAME? instead of the desired number

Any thoughts as to what I have done wrong here? I tried inputting it as a normal formula initially, then even tried it as an array formula, but still no result

cheers

 

[honkin]

I also found another formula online which purports to calculate longest streak, but have had no result with this either

Here is the array formula

=SORTN(FREQUENCY(IF((K5:K="WON")*LEN(K5:K), ROW(K5:K)),IF((K5:K="WON")*LEN(K5:K), , ROW(K5:K))), 1, 0, 1, 0)

Any help gladly accepted

cheers

 

[hagia_sofia]

Hello,

Ad 1) The formula will work only with Excel 365 (you seem to indicate 2016) + what language localization are you using?

Ad 2) It probably should be:

=MAX(FREQUENCY(IF((A1:A20="WON")*LEN(A1:A20),ROW(A1:A20)),IF((A1:A20="WON")*LEN(A1:A20),,ROW(A1:A20))))

 

[Phuoc]

Or not LEN()

=MAX(FREQUENCY(IF((A1:A20="WON"),ROW(A1:A20)),IF((A1:A20<>"WON"),ROW(A1:A20))))