Stupid Spreadsheet Tricks - Expected Wins

[Megalodan]

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:

Expected Wins  = [ ( Goals For per game) / (Goals Against per game) ] * (# of games in season / 2)

[I]note: whether you use "goals" or "goals per game" does not matter since they are (almost) the same and the denominators will cancel out[/I]

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.

Click to read more...

 

[Bear of Bad News]

You might like this paper by Alan Ryder:

http://www.hockeyanalytics.com/Research_files/Win_Probabilities.pdf

 

[Megalodan]

hell yeah i will. i knew this was too easy.

 

[Bear of Bad News]

I figured it'd be right up your alley - when I discovered the Pythagorean Theorem, I bet I spent weeks with that plus Excel.

Now I use it on my site to develop "support-neutral" wins and losses for goaltenders, such as:
http://hockeygoalies.org/bio/roy.html
http://hockeygoalies.org/bio/hasek.html
http://hockeygoalies.org/bio/brodeurm.html

(under regular season statistics, and postseason statistics, respectively)

 

[SHO NUFF*]

You might like this paper by Alan Ryder:

http://www.hockeyanalytics.com/Research_files/Win_Probabilities.pdf

In Ryder's pythagorean formula for hockey, he's using an exponent of 1.86. As this paper was written in 2004, he's accounting for ties. How would you adjust this exponent now that ties are no longer a factor?

 

[Bear of Bad News]

In Ryder's pythagorean formula for hockey, he's using an exponent of 1.86. As this paper was written in 2004, he's accounting for ties. How would you adjust this exponent now that ties are no longer a factor?

I haven't thought about it too much (and I actually just use 2 as my exponent in my work). For what I do (support-neutral goalie stats), I decided that it's preferable to use the standard formula (which assumes that all games are two-point games), because it puts Henrik Lundqvist on an even playing field with Patrick Roy on an even playing field with Jacques Plante.

The most straightforward way that I could think of would be to:

• Remove OT goals (and shootout effects) from the base totals.
• Use those goals and goals against to generate standings assuming no three-point games.
• To estimate the allocation of "third points" (from OT and SO) fit a regression model against OT/SO goals.
• Put it all back together.

Could be an interesting project for someone with a few hours to kill and an Excel spreadsheet.

 

[SHO NUFF*]

Ryder's using an exponent under 2 to account for ties because it reduces the expected winning percentage for a given ratio of GF to GA in the formula. With the elimination of ties, the exponent should now be adjusted to a higher number. It would seem at the very least that 2 becomes a more accurate figure. Do you think if one were to crunch the numbers it would be over 2?

 

[Bear of Bad News]

Well, first things first - with three-point games involved, the formula won't work at all (regardless of exponent chosen), since for a team with equal GF and GA, the formula will always project a winning percentage of 50%.

The question then becomes - once you adjust the formula, what happens to the optimal exponent?

 

[SHO NUFF*]

I'm interested in more of a game by game basis than a season long win/point expectation. I 'm trying to determine the most accurate Pythagorean Win Percentage based on my scoring estimates for each team on a per game basis.

 

[Bear of Bad News]

For single-game win probabilities, I'd start with something like Bill James' Log5 formulas:

http://www.chancesis.com/2010/10/03/the-origins-of-log5/

You'll have to make similar hockey modifications, since this was developed initially for baseball.

 

[Caeldan]

Well, first things first - with three-point games involved, the formula won't work at all (regardless of exponent chosen), since for a team with equal GF and GA, the formula will always project a winning percentage of 50%.

The question then becomes - once you adjust the formula, what happens to the optimal exponent?

Just random idea... would it be possible to convert to points % rather than win %?

Then I *think* an equal GF and GA would end up around 56% in terms of points %?

 

[Bear of Bad News]

Just random idea... would it be possible to convert to points % rather than win %?

Then I *think* an equal GF and GA would end up around 56% in terms of points %?

I'm using "winning percentage" and "points percentage" interchangeably, which is a bad habit of mine when talking hockey.

Your conclusion is correct.

 

[lakai17]

I go by knowledge of the game. Coaching systems and goaltending.

 

[Bear of Bad News]

I go by knowledge of the game. Coaching systems and goaltending.

Completely irrelevant to the thread, unless you choose to tie it in to numbers somehow.

 

[n00bxQb]

Just random idea... would it be possible to convert to points % rather than win %?

Then I *think* an equal GF and GA would end up around 56% in terms of points %?

Over the past 5 seasons, a game goes to OT/SO 23.794% of the time (1342 times in 5640 games)

Average points for regulation = 1 (2 points for win, 0 points for loss. 2/2 = 1)
Average points for overtime/shootout = 1.5 (2 points for win, 1 point for loss. 3/2 = 1.5)

So you could break it down to 0.76206*GP*(GF/GA)+0.23794*GP*(GF/GA)*1.5.

With an even goal differential, it calculates to 0.76206*82*1+0.23794*82*1*1.5 = 91.756 points or a 55.949% Point Percentage.

Great guess w/ 56%