I've been trying over the last couple of days to build a excel spreadsheet that can show sports standings and instead of placing one big post will all of the kinks, I've been placing one issue a post for help today my issue is with the "magic number".
The Magic number is defined as the smallest number such that any combination of wins by the first-place team and losses by the second-place team totaling the magic number guarantees that the first-place team will win the division. So for example in MLB Baseball's tight National League Central Division Race (a four/three team race) the standings would look like this (as of July 27, 2011) . . .
Team/Wins/Losses/Ties/Games Played/Win %/GB (Games Behind)/Magic #
1. St. Louis Cardinals/55/48/0/103/.534/-/60
2. Milwaukee Brewers/55/49/0/104/.529/.5/60
3. Pittsburgh Pirates/53/48/0/101/.525/1/62
4. Cincinnati Reds/50/53/0/103/.485/5/65
5. Chicago Cubs/42/61/0/103/.408/13/73
6. Houston Astros/33/70/0/103/.320/22/82
Now the equation that I have been using until this point was this . . .
=IF(E3=162, "Clinched", B4+(162-E3)-B3+1) where . . .
E3= games played, B3= division leader's wins, B4= another teams
Now with this formula I've got from the NL Central numbers that were a bit off from the above . . .
Team/ Magic or Elim #
1. St. Louis Cards/60
2. Milwaukee Brew/49
3. Pittsburgh Bucs/50
4. Cincinnati Reds/45
Beyond this issue there are also several other forms of magic numbers which are considered superior to that of the boilerplate version which I have shown you here . . .
For your Magic Number (for 1st Place teams ONLY)
1. First Place Clinch Number: The first place clinch number is the most similar to the commonly reported magic number. If the team wins this number of additional games, it is guaranteed to finish in at least a tie for first place in its division. An asterisk (*) indicates that even if this team wins all of its remaining games, it cannot guarantee itself a first-place finish.
2. Playoff Clinch Number: If a team wins this number of additional games, it is guaranteed to finish in at least a tie for a playoff spot. This could be as the division winner or as the wild-card team. Once again, an asterisk (*) indicates that even if this team wins all of its remaining games, it cannot guarantee itself a playoff berth.
For the Elimination Number (for all other teams NOT in 1st place)
1. First Place Elimination Number: This is the minimum number of games that this team must win in order to remain in contention for first place in its division. Teams which are already eliminated from first place contention are labeled "Elim" in this box.
2. Playoff Elimination Number: This is the minimum number of games that this team must win in order to remain in contention for a playoff spot. This could be either first place in the division or the wild card berth. Again, teams which are already eliminated from playoff contention are labeled "Elim" in this box.
So anyone who could help me with these "four" equations with the data I've provided would definitely get a thumbs up in my book. I'm not 100% sure how to write these formulas with the data I have here, but it would be great if someone figured them all out. Thanks again for the help in advance.
The Magic number is defined as the smallest number such that any combination of wins by the first-place team and losses by the second-place team totaling the magic number guarantees that the first-place team will win the division. So for example in MLB Baseball's tight National League Central Division Race (a four/three team race) the standings would look like this (as of July 27, 2011) . . .
Team/Wins/Losses/Ties/Games Played/Win %/GB (Games Behind)/Magic #
1. St. Louis Cardinals/55/48/0/103/.534/-/60
2. Milwaukee Brewers/55/49/0/104/.529/.5/60
3. Pittsburgh Pirates/53/48/0/101/.525/1/62
4. Cincinnati Reds/50/53/0/103/.485/5/65
5. Chicago Cubs/42/61/0/103/.408/13/73
6. Houston Astros/33/70/0/103/.320/22/82
Now the equation that I have been using until this point was this . . .
=IF(E3=162, "Clinched", B4+(162-E3)-B3+1) where . . .
E3= games played, B3= division leader's wins, B4= another teams
Now with this formula I've got from the NL Central numbers that were a bit off from the above . . .
Team/ Magic or Elim #
1. St. Louis Cards/60
2. Milwaukee Brew/49
3. Pittsburgh Bucs/50
4. Cincinnati Reds/45
Beyond this issue there are also several other forms of magic numbers which are considered superior to that of the boilerplate version which I have shown you here . . .
For your Magic Number (for 1st Place teams ONLY)
1. First Place Clinch Number: The first place clinch number is the most similar to the commonly reported magic number. If the team wins this number of additional games, it is guaranteed to finish in at least a tie for first place in its division. An asterisk (*) indicates that even if this team wins all of its remaining games, it cannot guarantee itself a first-place finish.
2. Playoff Clinch Number: If a team wins this number of additional games, it is guaranteed to finish in at least a tie for a playoff spot. This could be as the division winner or as the wild-card team. Once again, an asterisk (*) indicates that even if this team wins all of its remaining games, it cannot guarantee itself a playoff berth.
For the Elimination Number (for all other teams NOT in 1st place)
1. First Place Elimination Number: This is the minimum number of games that this team must win in order to remain in contention for first place in its division. Teams which are already eliminated from first place contention are labeled "Elim" in this box.
2. Playoff Elimination Number: This is the minimum number of games that this team must win in order to remain in contention for a playoff spot. This could be either first place in the division or the wild card berth. Again, teams which are already eliminated from playoff contention are labeled "Elim" in this box.
So anyone who could help me with these "four" equations with the data I've provided would definitely get a thumbs up in my book. I'm not 100% sure how to write these formulas with the data I have here, but it would be great if someone figured them all out. Thanks again for the help in advance.
