Calculate Magic Number - Excel

[dotcanada]

Good day.

I created a massive excel file where the standings and playoff picture updates itself after inputting the daily scores. My standings table is in the regular format (GP, W, L, OT, ROW, GF, GA, Diff, Pts, as well as Games Remaining).

I would like to do three things. Calculating the magic number on:
-clinching a playoff spot
-clinching a division
-when a team is out of the playoff run.

There was a thread on this but it was about 3 years old. Any help is great appreciated.

Click to read more...

 

[dotcanada]

Nevermind. I found Mayor Bee's post.

Please disregard this.

 

[Mayor Bee]

I can post mine later. If you can improve on the way I do it, I'd love to see it.

 

[dotcanada]

I can post mine later. If you can improve on the way I do it, I'd love to see it.

Hi Mayor. I thought I ask some questions regarding your calculations on this thread than your thread so I don't congest it too much.

I'm having a really hard time figuring out your results.

I know how you got the possible (or theoretical) points, but the following confuses me (this is as of 19 March):

Atlantic Division
Magic Numbers (control their own destiny)
Florida - 15 to clinch division spot
Tampa Bay - 17 to clinch division spot
Boston - 18 to clinch division spot

For Florida, I use the formula you provided (164-((Losses * 2)+OT/SO losses)) and I get 14. Same with Tampa Bay and Boston is 16 and 17 respectfully. Also, why don't you have "## to clinch playoff spot" and "## to clinch division" under the Atlantic division? Speaking of this what is the difference of "clinch division spot" and "clinch division", as indicated next to Washington?

What are your calculations for "## to clinch playoff spot" and "## to clinch division spot"?

For the cutoff lines, why do you have ROW indicated when it's not in any calculations?

I greatly appreciate your assistance in this. If you like to check out my excel file, I can publish it on my google drive. It's pretty big (about 13mb).

 

[Kane One]

Clinch division means they are the #1 seed in the division. Clinching a division spot means they clinch the playoffs but not as a wildcard team but as one of the top three teams in the division.

And using my math, those numbers are right but I don't use that same formula. I do (82 - other team GP) * 2 + other team Points - Points + 1.

In this case it's (82 - 72) * 2 + 83 - 89 + 1 which should come out to 15 for Florida. The "other team" I used was Detroit because they could achieve the most points out of every 4-or-below Atlantic Division seed.

 

[Mayor Bee]

Hi Mayor. I thought I ask some questions regarding your calculations on this thread than your thread so I don't congest it too much.

I'm having a really hard time figuring out your results.

I know how you got the possible (or theoretical) points, but the following confuses me (this is as of 19 March):

Atlantic Division
Magic Numbers (control their own destiny)
Florida - 15 to clinch division spot
Tampa Bay - 17 to clinch division spot
Boston - 18 to clinch division spot

For Florida, I use the formula you provided (164-((Losses * 2)+OT/SO losses)) and I get 14. Same with Tampa Bay and Boston is 16 and 17 respectfully. Also, why don't you have "## to clinch playoff spot" and "## to clinch division" under the Atlantic division? Speaking of this what is the difference of "clinch division spot" and "clinch division", as indicated next to Washington?

My setup goes for what I refer to as a "hard clinch", which is where Team A's points mathematically exceed Team B's possible points. Since magic numbers work in both directions (Team A's number of accumulated points goes up, but Team B's number of possible points goes down, and the magic number is affected by both), I make an adjustment that sets the formula for a magic number to +1. So Florida would need 15 points to be completely, 100% clear of the cutoff.

What are your calculations for "## to clinch playoff spot" and "## to clinch division spot"?

Clinching a playoff spot is when Team A's accumulated points exceed Team B's ability to reach that number. If they're tied, it goes to the tiebreakers.

A division spot is the same; once Team A is clear of the 4th-highest possible points total in their division, they've secured a spot.

For the cutoff lines, why do you have ROW indicated when it's not in any calculations?

Tiebreaking purposes.

I greatly appreciate your assistance in this. If you like to check out my excel file, I can publish it on my google drive. It's pretty big (about 13mb).

Sure, I can take a look at it.

For what it's worth, mine's something like 894 KB and includes 31 worksheets. 30 of them are each individual team's schedule for the season, and the last one has the overall standings. The standings page screencap is below.

 

[Mr Pipe]

I can post mine later. If you can improve on the way I do it, I'd love to see it.

Could you post your excel file, or PM it to me?
I'm actually using this as a way to learn excel and it'd be helpful if I had a guide to go off of.

Thanks