Hockey's Magic Number, Part 2: Excel Strikes Back

It turns out I lied to you last time. This didn't really get put on the backburner at all. Well maybe for a little while, but with a 7-hour break between classes on Mondays (9:30 to 11, then 6:30 to 10!!!), your mind starts wandering from your studies just a bit. So what's new? Extrapolation! Prediction! Algebra! More spreadsheets!

So come on in and join the party!

On Saturday, I just took the magic number formula that's been used in baseball for millenia and tinkered with it a bit so it played nice with hockey's point system. A quick refresher:

When MN(AB) goes below 1, then it is mathematically impossible for team B to finish ahead of team A in the standings. Every point gained by team A (2 for a win, one for an OTL) reduces the magic number, and every point lost by team B (2 for a loss, one for an OTL) reduces the magic number. It can only go down, and MN(AB) is not the same as MN(BA). The formula:

MagicNumber(AB) = 165 - Points(A) + Points(B) - (2 * Games Played(B))

It's quite a handy stat. But since it's based on the maximum points remaining for team B (i.e. they win every remaining game), team A's higher position is shown to be in jeopardy for most of the season. But who really thinks that the Blues, or the Thrashers, have any sort of a shot at the #1 seed? (If so, I'll give you a great deal on this bridge I have sitting around.)

So I thought; why not factor in the teams' points-per-game ratios? It's not a perfect solution, but with more than half the games played, I'd say we have a large enough sample to make a good evaluation of a team's capabilities. Suddenly not losing for a whole month doesn't happen very often, and it happens even less often to teams on the margin. We know who's good, and we know who isn't, and it's unlikely to change.

On the other hand, the Stars were awful at the beginning of the season. But like someone coming out of hypnosis cured of their ills ("You will wake up when you hear the magic words..."), they've taken hold of a solid #5 seed based on PPG, behind the division leaders and the Blackhawks, and they're creeping up on the Flames. Hell, they might even be neck-and-neck with the 'Hawks if it weren't for those early results dragging their averages down. So take all this with a grain of salt.

After trying to re-invent the wheel for a while in figuring this out, I took the easy approach. The Expected Magic Number is calculated by taking team B's PPG multiplied by 82, subtracting team A's current point total, and adding 1. In other words, how many more points does team A need to exceed the amount of points that team B is on pace to finish with? The equation:

ExpectedMagicNumber(AB) = (82 * PPG(B)) - Points(A) + 1

Simple, right? Like the true magic number, a win or an OTL for team A will bring the EMN closer to zero, and a loss won't change it. But since we're not setting the standard for team B at two points per game anymore, team B's results can make the EMN go up. For every game that B does better than their original PPG says they should, their new PPG will be higher, making their season pace higher, setting the bar higher for team A.

Tempering this, however, are the facts that individual games are worth less towards PPG as both games played and total points increase. Based on yesterday morning's standings, if the Sharks win their next game, their PPG increases by about 0.008; and if the Islanders win their next game their PPG increases by about 0.026 -- three times as much. How much does this change the EMN? Against the Bruins, the Devils only reclaim one point with a win, the mid-table teams in the East get about 1.5, and the Thrashers and Islanders get 1.95 and 2.1, respectively. So a bad team on a hot streak can hold their EMN steady, even if the real magic number is on a steady march down towards zero. Like I said, not perfect.

But we're only halfway through the story. Next time, we throw team A's PPG into the mix, and I'll take a look at the wild card playoff numbers. I know you can't wait!

Until then, have fun with this, updated with the current numbers:

via docs.google.com