Longest winning streak

[pepe74287]

Hi all,

Given the data below, where team is awarded 3 points for a win, 1 point for a draw and 0 for a loss, how can I calculate the longest winning overall streak for team in G1? I got a formula for longest home winning streak:

=MAX(FREQUENCY(IF(A2:A36=$G$1,IF(D2:D36<>"",IF(D2:D36=3,ROW(A2:A36)))),IF(A2:A36=$G$1,IF(D2:D36<>"",IF(D2:D36<3,ROW(A2:A36)))))) CSE , but can't figure out how to adjust the formula for overall winning streak.

Any help is much appriciated.

Pepe

[TABLE="width: 571"]
<tbody>[TR]
[TD]Home team[/TD]
[TD]Away team[/TD]
[TD]Result[/TD]
[TD]Points Home[/TD]
[TD]Points away[/TD]
[TD]Team:[/TD]
[TD]Plzeň[/TD]
[/TR]
[TR]
[TD]1. round[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plzeň[/TD]
[TD]Blšany[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="colspan: 2"]Longest winning home streak[/TD]
[/TR]
[TR]
[TD]Bohemians 1905[/TD]
[TD]Slovácko[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Liberec[/TD]
[TD]Drnovice[/TD]
[TD]2:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ostrava[/TD]
[TD]Slavia[/TD]
[TD]1:3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sparta[/TD]
[TD]Č. Budějovice[/TD]
[TD]5:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teplice[/TD]
[TD]Jablonec[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Olomouc[/TD]
[TD]Žižkov[/TD]
[TD]4:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Příbram[/TD]
[TD]Zbrojovka[/TD]
[TD]3:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2. round[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blšany[/TD]
[TD]Olomouc[/TD]
[TD]0:0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Příbram[/TD]
[TD]Teplice[/TD]
[TD]0:1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zbrojovka[/TD]
[TD]Sparta[/TD]
[TD]0:2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Žižkov[/TD]
[TD]Ostrava[/TD]
[TD]1:2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jablonec[/TD]
[TD]Liberec[/TD]
[TD]1:3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Č. Budějovice[/TD]
[TD]Bohemians 1905[/TD]
[TD]0:1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slavia[/TD]
[TD]Plzeň[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drnovice[/TD]
[TD]Slovácko[/TD]
[TD]0:2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3. round[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slovácko[/TD]
[TD]Blšany[/TD]
[TD]1:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plzeň[/TD]
[TD]Drnovice[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bohemians 1905[/TD]
[TD]Slavia[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liberec[/TD]
[TD]Č. Budějovice[/TD]
[TD]2:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ostrava[/TD]
[TD]Jablonec[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sparta[/TD]
[TD]Žižkov[/TD]
[TD]1:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teplice[/TD]
[TD]Zbrojovka[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Olomouc[/TD]
[TD]Příbram[/TD]
[TD]3:2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4. round[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blšany[/TD]
[TD]Příbram[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zbrojovka[/TD]
[TD]Olomouc[/TD]
[TD]0:2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Žižkov[/TD]
[TD]Teplice[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jablonec[/TD]
[TD]Sparta[/TD]
[TD]0:3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Č. Budějovice[/TD]
[TD]Ostrava[/TD]
[TD]1:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slavia[/TD]
[TD]Liberec[/TD]
[TD]1:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drnovice[/TD]
[TD]Bohemians 1905[/TD]
[TD]2:1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slovácko[/TD]
[TD]Plzeň[/TD]
[TD]5:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi.

You can use simply:

=MAX(FREQUENCY(IF(A2:B36=$G$1,IF(D2:E36=3,ROW(D2:E36))),IF(A2:B36=$G$1,IF(D2:E36<>3,ROW(D2:E36)))))

Regards

 

[pepe74287]

Thanks a lot, this was much easier than I expected

 

[XOR LX]

You're welcome!