Calculate win/loss percentage based on multiple criteria?

[parvezs27]

Hi all!

I have a dataset for Australian Football, it contains data for the matches played between teams in a particular league in 2021. So rows 2 and 3 for example contain the data and statistics for the match played between Wangaratta Magpies and Albury in Round 1 of the league.

I want to calculate the percentage of times teams win or lose a match if they win a statistic e.g. "Total Disposals".

For example, if we just look at the match in rows 2 and 3 between Wangaratta Magpies and Albury, we can see in column D "Total Disposals" that Albury had more Total Disposals (321) than Wangaratta Magpies (318), so they won that statistic as denoted by a "W" in column E. However, they lost the match as denoted by "L" in column C. Basically I just want see how many times this happens where a team wins the statistic "Total Disposals" and either win or lose the match and get this into a percentage. So a final conclusion may be "Teams in this league win 75% of their matches if they have more total disposals than the other team".

I hope this is clear, happy to clear up any confusion. I have attached a small section of the data as a mini sheet.

Thanks!

Ovens and Murray League Macro Enabled.xlsm
	A	B	C	D	E	F	G	H	I
1	Round	Team	Win/Lose	Total Disposals	Total Disposals W/L	Total Disposals Differential	Contested Disposals	Contested Disposals W/L	Contested Disposals Differential
2	1	Wangaratta Magpies	W	318	L	-3	53	L	-28
3	1	Albury	L	321	W	3	81	W	28
4	1	Lavington	L	275	L	-80	91	W	8
5	1	Corowa-Rutherglen	W	355	W	80	83	L	-8
6	1	Myrtleford	W	270	L	-16	85	W	3
7	1	Wangaratta Rovers	L	286	W	16	82	L	-3
8	2	Albury	W	395	W	10	122	W	18
9	2	Wodonga	L	385	L	-10	104	L	-18
10	2	Wangaratta Magpies	W	347	W	54	138	W	45
11	2	Wangaratta Rovers	L	293	L	-54	93	L	-45
Match Data

Cell Formulas
Range	Formula
E2,E4,E6,E8,E10,H2,H4,H6,H8,H10	E2	=IF(D2>D3,"W",IF(D2=D3,"D","L"))
F2,F4,F6,F8,F10,I2,I4,I6,I8,I10	F2	=D2-D3
E3,E5,E7,E9,E11,H3,H5,H7,H9,H11	E3	=IF(D3>D2,"W",IF(D3=D2,"D","L"))
F3,F5,F7,F9,F11,I3,I5,I7,I9,I11	F3	=D3-D2

 

[DRSteele]

I wonder why the data is laid out like this, where one row is a team and the next is its opponent. Did you put it in this form? It surely makes manipulating the data a painful exercise.

Usually the data for sports league schedules has columns for Visitor, Host, Visitor Score, Host Score, Winner, Visitor Contested Disposals, etc.. See here: this is a picture of my data-set for the NHL. It goes off to the right with dozens of statistics, some from the data source and some calculated. From an Excel Table like this, any and all calculations can be done for the teams, in aggregate or by filtering to make reports like standings and other performance data.

 

[parvezs27]

I wonder why the data is laid out like this, where one row is a team and the next is its opponent. Did you put it in this form? It surely makes manipulating the data a painful exercise.

Usually the data for sports league schedules has columns for Visitor, Host, Visitor Score, Host Score, Winner, Visitor Contested Disposals, etc.. See here: this is a picture of my data-set for the NHL. It goes off to the right with dozens of statistics, some from the data source and some calculated. From an Excel Table like this, any and all calculations can be done for the teams, in aggregate or by filtering to make reports like standings and other performance data.

View attachment 84096

Thank you for sharing this. I am quite new to sports data so I didn't know the ideal way to structure it. Do you have any resources which could help out with sports analytics e.g. how to structure the data or how to do certain calculations? Thanks again!