Help in counting streaks

[dungeon1976]

Hello,
I have been working on a spreadsheet to follow my country’s national soccer league.
I want to find a way to count some different streaks for each team.
Here is a sample of what I have so far from A1:AI7:

[TABLE="width: 815"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 3[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 4[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 5[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 6[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The numbers on the first row are the Matchdays, D stands for “Draw”, L stands for “Loss” and W stands for “Win”.
All the “blank” cells have a formula that automatically fills each cell with the correct value when the game is played.
I have googled around and found some formulas that appear to be working, but others don’t.
[TABLE="width: 1179"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AM[/TD]
[TD]AN[/TD]
[TD]AO[/TD]
[TD]AP[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Current Streak[/TD]
[TD]Longest Winning Streak[/TD]
[TD]Longest Draw Streak[/TD]
[TD]Longest Losing Streak[/TD]
[TD]Longest No-win streak (D and L streak)[/TD]
[TD]Longest No-lose streak (D and W streak)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Team 1[/TD]
[TD]1 D[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Team 2[/TD]
[TD]2 L[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Team 3[/TD]
[TD]1 L[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Team 4[/TD]
[TD]1 D[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Team 5[/TD]
[TD]3 L[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Team 6[/TD]
[TD]2 L[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


AK2: Current Streak

{=IFERROR(LOOKUP(1E+100,FREQUENCY(IF(B2:AI2=LOOKUP(2,1/(B2:AI2<>""),B2:AI2), COLUMN(B2:AI2)),IF(B2:AI2<>LOOKUP(2,1/(B2:AI2<>""),B2:AI2),IF(B2:AI2<>"",COLUMN(B2:AI2)))))&" "&LOOKUP(2,1/(B2:AI2<>""),B2:AI2),"")}

This one seems to be working.

AL2: Longest Winning Streak

{=MAX(FREQUENCY(IF ($B2:$AI2="W",COLUMN($B2:$AI2)),IF($B2:$AI2<>"W",COLUMN($B2:$AI2))))}

This one seems to be working.

AM2: Longest Draw Streak

{=MAX(FREQUENCY(IF($B2:$AI2="D",COLUMN($B2:$AI2)),IF($B2:$AI2<>"D", COLUMN($B2:$AI2))))}

This one seems to be working.

AN2: Longest Losing Streak

{=MAX(FREQUENCY(IF($B2:$AI2="L",COLUMN($B2:$AI2)),IF($B2:$AI2<>"L",COLUMN($B2:$AI2))))}

This one seems to be working.

AO2: Longest No-win streak (D and L streak)

{=MAX(FREQUENCY(IF($B2:$AI2<>"W",COLUMN($B2:$AI2)),IF($B2:$AI2="W",COLUMN($B2:$AI2))))-COUNTIF($B2:$AI2,"")}

This one seems to be working but I do not know for sure.

AP2: Longest No-lose streak (D and W streak)

{=MAX(FREQUENCY(IF($B2:$AI2<>"L",COL($B2:$AI2)),IF($B2:$AI2="L",COL($B2:$AI2))))-COUNTIF($B2:$AI2,"")}

This is not working: AP4 should be 2 and not 0 but on AP5 it is correct. I do not know how to work this out. As all the range is selected I have used the COUNTIF formula to subtract the empty cells otherwise the result would be even stranger…

Would it be possible for you to check if is there anything wrong with these formulas (at least on the last I know there is).

I would like to do this without having to use VBA code.

All help would be appreciated.

Thank you.

 

[pgc01]

Hi
Welcome to the board

Try in AP2:

=MAX(FREQUENCY(IF((B2:AI2="W")+(B2:AI2="D"),COLUMN(B2:AI2)),IF((B2:AI2<>"W")*(B2:AI2<>"D"),COLUMN(B2:AI2))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

 

[dungeon1976]

Hi
Welcome to the board

Try in AP2:

=MAX(FREQUENCY(IF((B2:AI2="W")+(B2:AI2="D"),COLUMN(B2:AI2)),IF((B2:AI2<>"W")*(B2:AI2<>"D"),COLUMN(B2:AI2))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Copy down

Thank you for the welcoming.

I have tried the formula and it worked like a charm!!!

I have used it on AO2 and it looked like the formula I had was not working properly, so I am using it on AO column.

Have you checked the other formulas I have posted? Is there any way I can simplify them by using the same logic you have used in this one?

Thanks a lot for the help on this one. Please let me know about the others, if possible.

 

[dungeon1976]

You can mark this thread as solved. This problem is solved with your help pgc01.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.