View Single Post
05-04-2013, 05:51 PM
#1
Megalodan
Registered Loser

Join Date: Mar 2011
Location: Drinkscotch Center
Country:
Posts: 855
vCash: 500
Stupid Spreadsheet Tricks - Expected Wins

Basic facts:
• Hockey teams win games by outscoring their opponent; therefore, a simple indicator of a team's seasonal success is the number of goals scored against goals allowed.
• The NHL's 'goal differential' (goals scored - goals allowed) is misleading because it does not account for the quantities of each, only the difference between them.
• A better indicator is "Goals per Goals Against". This ratio indicates the ability of a team to outscore their opponent on any given night, by comparing goals scored directly to goals allowed.

There are several variations and interesting experiments that can be done with this stat, but my favorite is calculating how many games a team can expect to win with their 'goal ratio'.

If we assume that a team with a 1.00 ratio (exactly one goal scored for every goal allowed) will win exactly half of their games, then we can calculate the number of games a team should have won:
Code:
```Expected Wins  = [ ( Goals For per game) / (Goals Against per game) ] * (# of games in season / 2)

note: whether you use "goals" or "goals per game" does not matter since they are (almost) the same and the denominators will cancel out```
If you run that against the 2013 stats (as well as other seasons) you will get a table like the one below. The teams are ranked by the number of wins they had this season. The expected wins for each team is also calculated, and a second ranking is made based on this stat. A 'differential' indicates how many ranks a club 'unfairly' rose or fell from expectations. The numbers are hauntingly accurate.

Please leave any questions, comments, or criticisms for me. I've done this as a hobby for the last few seasons, and this is the first time I've wrote up anything about. Thanks for reading.

 Rank Team GP W L OT G/G GA/G Goals/GA Expected Wins Exp. Rank Rank. Diff 1 CHICAGO 48 36 7 5 3.1 2.02 1.534653465 36.83 1 0 2 PITTSBURGH 48 36 12 0 3.38 2.48 1.362903226 32.71 2 0 3 ANAHEIM 48 30 12 6 2.79 2.4 1.1625 27.90 6 3 4 MONTREAL 48 29 14 5 3.04 2.58 1.178294574 28.28 4 0 5 ST LOUIS 48 29 17 2 2.58 2.38 1.084033613 26.02 12 7 6 BOSTON 48 28 14 6 2.65 2.21 1.199095023 28.78 3 -3 7 LOS ANGELES 48 27 16 5 2.73 2.38 1.147058824 27.53 7 0 8 WASHINGTON 48 27 18 3 3.04 2.71 1.121771218 26.92 9 1 9 VANCOUVER 48 26 15 7 2.54 2.4 1.058333333 25.40 13 4 10 TORONTO 48 26 17 5 3.02 2.67 1.131086142 27.15 8 -2 11 NY RANGERS 48 26 18 4 2.62 2.25 1.164444444 27.95 5 -6 12 MINNESOTA 48 26 19 3 2.46 2.6 0.946153846 22.71 20 8 13 SAN JOSE 48 25 16 7 2.42 2.33 1.038626609 24.93 14 1 14 OTTAWA 48 25 17 6 2.33 2.08 1.120192308 26.88 10 -4 15 DETROIT 48 24 16 8 2.54 2.29 1.109170306 26.62 11 -4 16 COLUMBUS 48 24 17 7 2.4 2.4 1 24.00 15 -1 17 NY ISLANDERS 48 24 17 7 2.81 2.83 0.992932862 23.83 17 0 18 WINNIPEG 48 24 21 3 2.62 2.94 0.891156463 21.39 24 6 19 PHILADELPHIA 48 23 22 3 2.75 2.9 0.948275862 22.76 19 0 20 DALLAS 48 22 22 4 2.67 2.94 0.908163265 21.80 22 2 21 PHOENIX 48 21 18 9 2.52 2.6 0.969230769 23.26 18 -3 22 BUFFALO 48 21 21 6 2.46 2.9 0.848275862 20.36 25 3 23 NEW JERSEY 48 19 19 10 2.29 2.54 0.901574803 21.64 23 0 24 EDMONTON 48 19 22 7 2.56 2.73 0.937728938 22.51 21 -3 25 CAROLINA 48 19 25 4 2.65 3.31 0.80060423 19.21 28 3 26 CALGARY 48 19 25 4 2.67 3.27 0.816513761 19.60 27 1 27 TAMPA BAY 48 18 26 4 3.06 3.06 1 24.00 16 -11 28 NASHVILLE 48 16 23 9 2.27 2.77 0.819494585 19.67 26 -2 29 COLORADO 48 16 25 7 2.38 3.12 0.762820513 18.31 29 0 30 FLORIDA 48 15 27 6 2.27 3.54 0.641242938 15.39 30 0

The clubs that are closer to a 1.00 goal ratio are more likely to rise/fall in the standings unfairly. I hypothesize that this fluctuation is based on the number of overtime games these clubs play, and their ability to take wins out of them.