Hi all
I have this soccer results with the goals splitted by teams, but i can't find the way to calculate the current winning streaks, the current draw streaks and the current loss streaks.
Note: the most recent results at bottom of the column.
Also i have the formula for calculate the 1st Longest Streak, but how can I get the 2nd, 3rd etc. longest streaks?
any help would be a lot appreciated!
Thanks
matricial formulas used so far:
<v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501>
Win 1st Longest Streak:
=MAX(FREQUENCY(IF(U2:U501<>"",IF(U2:U501>V2:V501,ROW(U2:U501))),IF(U2:U501<=V2:V501,ROW(V2:V501))))
Draw 1st Longest Streak:
=MAX(FREQUENCY(IF(U2:U501<>"",IF(U2:U501=V2:V501,ROW(U2:U501))),IF(V2:V501<>"",IF(U2:U501<>V2:V501,ROW(U2:U501)))))<v2:v501,row(u2:u501))),if(u2:u501>
[TABLE="width: 96"]
<tbody>[TR]
[TD]FT Team[/TD]
[TD]FT Opp.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
<v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501>sample spreadsheet:
https://mega.nz/#!m9ghBQaY!u1YA8sQJMfzzeEplybHxUnwZn5ty_QDoNXtpFhc9Cqw</v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501>
I have this soccer results with the goals splitted by teams, but i can't find the way to calculate the current winning streaks, the current draw streaks and the current loss streaks.
Note: the most recent results at bottom of the column.
Also i have the formula for calculate the 1st Longest Streak, but how can I get the 2nd, 3rd etc. longest streaks?
any help would be a lot appreciated!
Thanks
matricial formulas used so far:
<v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501>
Win 1st Longest Streak:
=MAX(FREQUENCY(IF(U2:U501<>"",IF(U2:U501>V2:V501,ROW(U2:U501))),IF(U2:U501<=V2:V501,ROW(V2:V501))))
Draw 1st Longest Streak:
=MAX(FREQUENCY(IF(U2:U501<>"",IF(U2:U501=V2:V501,ROW(U2:U501))),IF(V2:V501<>"",IF(U2:U501<>V2:V501,ROW(U2:U501)))))<v2:v501,row(u2:u501))),if(u2:u501>
[TABLE="width: 96"]
<tbody>[TR]
[TD]FT Team[/TD]
[TD]FT Opp.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
<v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501><v2:v501,row(u2:u501))),if(u2:u501>sample spreadsheet:
https://mega.nz/#!m9ghBQaY!u1YA8sQJMfzzeEplybHxUnwZn5ty_QDoNXtpFhc9Cqw</v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501></v2:v501,row(u2:u501))),if(u2:u501>
Last edited: