counting frequnce of wins and losses, how?

[Michala]

Hello guys,

I have a database of about 40 000 matches that I need to analyze. This is only an example of how the data look like (you'll get the idea from the table)

[TABLE="width: 500"]
<tbody>[TR]
[TD]round1
[/TD]
[TD]Winner (J)
[/TD]
[TD]Looser (K)
[/TD]
[TD]Ordred for Zverev[/TD]
[TD]series[/TD]
[/TR]
[TR]
[TD]round1[/TD]
[TD]Zverev[/TD]
[TD]Federer[/TD]
[TD]1[/TD]
[TD]W1[/TD]
[/TR]
[TR]
[TD]round1[/TD]
[TD]Nieminen[/TD]
[TD]Becer[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]round2[/TD]
[TD]Karlovic[/TD]
[TD]Tsonga[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]round2[/TD]
[TD]Zverev[/TD]
[TD]Berdych[/TD]
[TD]1[/TD]
[TD]W2[/TD]
[/TR]
[TR]
[TD]quarter[/TD]
[TD]Lend[/TD]
[TD]Edberg[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]quarter[/TD]
[TD]Borg[/TD]
[TD]Agassi[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]quarter[/TD]
[TD]Krajicek[/TD]
[TD]Karlovic[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]quarter[/TD]
[TD]Sampras[/TD]
[TD]Zverev[/TD]
[TD]-1[/TD]
[TD]L1[/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is:
To caculate for each player the consecutive wins or losses and the frequency of these steaks
(dont forget, there are 40 000 items and god knows how many palyers)

For example, if I were to indiviually search the data I would find out that Zverev had the following series: L1, W4, L2, W6, L3 etc.
( L stands for loss; W for win; and the nubmer next to them signifinifies how many times in a row did they happen)

So the finale table for Zverev would be, for example:
a) 55 times W1
b) 32 times W2
c)12 times W3
d) 14 times L5
.....
with longest streak W14
and average winning streak of W5
and average losing streak of L4


How can I extract this information from such a table in the most effective and economical way?
Please help!

Michaela

P.S.
My thoughts thus far are:

1. insert a separate column where I will select for each individual player.
For example, for Zverev, I would use this formula:
=IF(J2="Zverev",1,IF(K2="Zverev",-1,IF(NOT(OR(K2="Zverev",B5="Zverev")),0)))

This would give me a column of 1s,0s, and -1 (as in the table above)

2. Clean the data of the "0s" (don't know how to do that???)

3. Convert the row of 1s and -1s into W and L values
e.g.
W1
W2
L3 etc

4. Lastly use the countif function to get the frequence for each category (i.e W1...Wn..L1..Ln)

Problems:
1. I would have to search manually for each player and change the formula to his name
2. Even if I manage to get rid of the "zeros" in the series and have only 1s and -1 (i.e. W1, W2, L3 etc. ) there's clearly a difference between an independent W1 (sandwiched between L1 and L1) and W1 which is part of a series from W1..to W4 (here you want to count the last only).

 

[DRSteele]

Welcome to the forum.

With such a small amount of data on which to experiment, I found this difficult. I adapted formulas I use to track the NHL seasons.

The data is in an Excel Table named T_Scr, so the formulas are Structured References. The formulas are for Winning and Losing Streaks of 1, 2, and 3 and can be extended out rightways as far as you like.

Book1
	E	F	G	H	I	J	K	L	M	N	O
1	Round	Winner (J)	Loser (K)
2	round1	Zverev	Federer
3	round1	Nieminen	Becer
4	round2	Karlovic	Tsonga
5	round2	Zverev	Berdych
6	quarter	Lend	Edberg
7	quarter	Borg	Agassi
8	quarter	Krajicek	Karlovic
9	quarter	Sampras	Zverev
10
11		winning streaks				losing streaks				longest	longest
12		1	2	3		1	2	3		winning	losing
13	Zverev	0	1	0		1	0	0		2	1
Sheet55

Cell Formulas
Range	Formula
F13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=F$12))}
G13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=G$12))}
H13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=H$12))}
J13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=J$12))}
K13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=K$12))}
L13	{=SUM(--(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))))=L$12))}
N13	{=MAX(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Winner (J)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]])))))}
O13	{=MAX(FREQUENCY(IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]=$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]]))),IF(T_Scr[[Winner (J)]:[Loser (K)]]=$E13,IF(T_Scr[Loser (K)]<>$E13,ROW(T_Scr[[Winner (J)]:[Loser (K)]])))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Michala]

Oh my God, DRSteele, you are amazing! I expected someone to just give me general points, not the equations. Thank you so much!
Now i have to admit I am not sure how you did this..don't know these functions.But it seems to me that you are not even copying the names, just the functions into the right cell. That would make it so much quicker and easier to me!


You mentioned two things:
1. that you can't properly test it on my data. I'd actually like you to provide you with a real sample....but how to do it here?

2. I am actually really interested in using this for NHL teams as well. But there the data I have for them look a bit different. So it'd be cool to understand your equations too.

Again, I really appreciate your help.
Michaela

 

[DRSteele]

You're welcome.

Yes, Structured References are super that way: you don't even need to be on the same sheet to construct formulas that make reference to their Excel Tables. [There is one peculiarity about them, however: to copy rightways, the Field names (i.e., the columns, like Winner) will not be absolute but relative references if you drag right to copy, so you have to copy-then-paste if you wish to avoid that problem.]

I'm pretty sure the formulas do what you require. You can't share a workbook on this Forum, but you can use the MrExcel HTML Maker (see my signature) to post a grater sample size.

For the NHL, I have a massive Excel Table of all the scores for 1271 games which I populate by using PowerQuery to get the scores from http://www.hockey-reference.com/leagues/NHL_2018_games.html This Table also contains various calculations and represents the source for all kinds of analysis, including our winning streaks, losing streaks, points streaks, winless streaks, current streaks, etc.

If you wish to learn about Excel Tables and Structured References, some of these and other videos from the excellent ExcelIsFun channel on youtube might just interest you. https://www.youtube.com/watch?v=7rf10c-E8EE&list=PLrRPvpgDmw0kmoAhXamTJw6XkebJ6gCxg This and other videos might help you understand function FREQUENCY. ​https://www.youtube.com/watch?v=uUrI8hoj8BA_{^{<strike>
</strike>}}​

 

[Michala]

You're welcome.

Yes, Structured References are super that way: you don't even need to be on the same sheet to construct formulas that make reference to their Excel Tables. [There is one peculiarity about them, however: to copy rightways, the Field names (i.e., the columns, like Winner) will not be absolute but relative references if you drag right to copy, so you have to copy-then-paste if you wish to avoid that problem.]

I'm pretty sure the formulas do what you require. You can't share a workbook on this Forum, but you can use the MrExcel HTML Maker (see my signature) to post a grater sample size.

I'll try to do that over the weekend and maybe shoot you a PM (if you don't mind?) once I manage to somehow get the data uploaded.

For the NHL, I have a massive Excel Table of all the scores for 1271 games which I populate by using PowerQuery to get the scores from http://www.hockey-reference.com/leagues/NHL_2018_games.html This Table also contains various calculations and represents the source for all kinds of analysis, including our winning streaks, losing streaks, points streaks, winless streaks, current streaks, etc.

How cool! I am actually using the same webpage and their data (haven't found anything better yet..and haven't found anything on AHL). I'd love to see the results that you have for 2018! Actually I want to do that not just for the 2018/2017 season, but also for the previous seasons (I think that www.hockey-reference.com goes back to 2013).


Since you're from Calgary, I take it that you know one of our hockey players - he didn't have a good stint with the Flames, but he's a good player otherwise. Jagr, might have heard of him before
Too bad that Flames didn't live up to the expectations this season - the team has some real good players but it has not come together for them. My sense is that the coach might be let go.

btw. hho's going to win the SC? (Bruins been looking mighty good recently).

If you wish to learn about Excel Tables and Structured References, some of these and other videos from the excellent ExcelIsFun channel on youtube might just interest you. https://www.youtube.com/watch?v=7rf10c-E8EE&list=PLrRPvpgDmw0kmoAhXamTJw6XkebJ6gCxg This and other videos might help you understand function FREQUENCY. ​https://www.youtube.com/watch?v=uUrI8hoj8BA_{^{<strike>
</strike>}}

​

This is perfect, thank you. My weekend is now all organized.

Michaela

 

[DRSteele]

No need for a PM...I get a notification to this thread.

We'll get you started on PowerQuery. We'd be silly monkeys to be typing in daily scores for 1271 matches, so let's use tech to our advantage.

Goodness, everyone knows Jagr! He's a legend, HOHF for sure. But he has advanced beyond his best-before date - it was a silly roster move, although some of Calgary's youngsters benefitted immensely from his tutelage. Calgary's collapse after game 70 fills me with the urge to...well, I can't say. Nashville is my prediction...but what do I know? I thought Vegas would fail miserably and Edmonton would steamroll over the West.

Have fun watching all those videos.

 

[Michala]

No need for a PM...I get a notification to this thread.

We'll get you started on PowerQuery. We'd be silly monkeys to be typing in daily scores for 1271 matches, so let's use tech to our advantage.

Goodness, everyone knows Jagr! He's a legend, HOHF for sure. But he has advanced beyond his best-before date - it was a silly roster move, although some of Calgary's youngsters benefitted immensely from his tutelage. Calgary's collapse after game 70 fills me with the urge to...well, I can't say. Nashville is my prediction...but what do I know? I thought Vegas would fail miserably and Edmonton would steamroll over the West.

Have fun watching all those videos.

cool, so I'll reply when I am ready.
Regarding hockey: yeah, Jarda is a legend here too - owns an entire hockey franchise - Kladno (which produced a lot of good NHL players in the past, not so much now). As for Jagr and the Flames..yeah the fit wasn't great - neither for Jagr nor for the team. Johnny might have learned a thing or two and Bennet as well, but overall this was surely a huge disapointment.
After last season, I thought that McDavid's Edmonton will be amazing this year. Thus far, it's just him. But he's truly something special to watch..it's so sad that he landed in an organisation that doesn't seem to be able to put it together.
yeah, Vegas is definitely a big surprise. but I'd say that Gerad's coaching is great - he did a lot of good in Florida, before they fired him (can't really understand what the management is thinking there?).

Well well, Toronto is a team that's up and coming. So it's not all bad for Canada. There too, though, you can see how much a good manager and a coach can do..just a few years ago, it a horrible team. Now, potentially a dark horse.