Tying team name to team record:Completely Stuck!

KC2MF · Aug 21, 2011

I'm creating a spreadsheet to compile sports standings (currently using baseball as the sport) and I can't seem to find out how to automatically sort (update) the standings once a game is won or lost by a team. Here is how my standings look (formulas and all) looking at the 2010 AL East Standings on the final day of the season.

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">AL East</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tampa Bay</td><td style="text-align: right;;">96</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5926</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">New York (AL)</td><td style="text-align: right;;">95</td><td style="text-align: right;;">67</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5864</td><td style="text-align: right;;">1</td><td style="border-top: 1px solid black;;">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Boston</td><td style="text-align: right;;">89</td><td style="text-align: right;;">73</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5494</td><td style="text-align: right;;">7</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Toronto</td><td style="text-align: right;;">85</td><td style="text-align: right;;">77</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5247</td><td style="text-align: right;;">11</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Baltimore</td><td style="text-align: right;;">66</td><td style="text-align: right;;">96</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4074</td><td style="text-align: right;;">30</td><td style=";">Eliminated</td></tr></tbody></table>Division <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=SUM(B3:D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=162-E3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=(B3+(D3/2))/(B3+C3+D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=IF(MAX($G$3:$G$7)=G3,"Leader",((MAX($B$3:$B$7)-B3)+(C3-MIN($C$3:$C$7)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=IF(B4+(162-E3)-B3+1<1,"Clinched",B4+(162-E3)-B3+1)</td></tr></tbody></table></td></tr></table> 

Had the (New York) Yankees won and the (Tampa Bay) Rays lost in their last game it would have placed New York on top over Tampa Bay by 1 game (since they were tied). Unfortunately, I have no way to simply have the team name move up or down relative to the other records in that teams division, league, conference, etc. If anyone could help me with this tough one I would be greatly appreciated. Thanks in advance for reading and for your help.

CWatts · Aug 21, 2011

Well if I'm understanding, you have three choices.

1. Sort Manually
2. Use VBA sort
3. Use some helper cells (either hidden or separate sheet) to use the RANK function and then do lookups if you'd like to avoid vba.

As long as Boston is ahead of NY, it's all good.

KC2MF · Aug 22, 2011

Thanks CWatts for the sound advice

I thought it only fitting to leave you with the picture that you that you so aptly described from the record book. This being from the final day of the 2008 Season with the standings from the AL East again. I haven't decided whether I'll plaster the Wild Card Games Behind and Magic/Tragic numbers yet but for this flashback I thought it appropriate . . .

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td><td style="font-weight: bold;;">WC M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">2008 AL East</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tampa Bay</td><td style="text-align: right;;">97</td><td style="text-align: right;;">65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5988</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Boston</td><td style="text-align: right;;">95</td><td style="text-align: right;;">67</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5864</td><td style="text-align: right;;">2</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="border-bottom: 1px solid black;;">Clinched!</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">New York (AL)</td><td style="text-align: right;;">89</td><td style="text-align: right;;">73</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5494</td><td style="text-align: right;;">8</td><td style=";">Eliminated</td><td style="border-top: 1px solid black;;">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Toronto</td><td style="text-align: right;;">86</td><td style="text-align: right;;">76</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5309</td><td style="text-align: right;;">11</td><td style=";">Eliminated</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Baltimore</td><td style="text-align: right;;">68</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">161</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.4224</td><td style="text-align: right;;">28.5</td><td style=";">Eliminated</td><td style=";">Eliminated</td></tr></tbody></table>Division 
And as a bonus, here are the final standings of the 2007 AL East . . .

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td><td style="font-weight: bold;;">WC M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">2007 AL East</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Boston</td><td style="text-align: right;;">96</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5926</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">New York (AL)</td><td style="text-align: right;;">94</td><td style="text-align: right;;">68</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5802</td><td style="text-align: right;;">2</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="border-bottom: 1px solid black;;">Clinched!</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Toronto</td><td style="text-align: right;;">83</td><td style="text-align: right;;">79</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5123</td><td style="text-align: right;;">13</td><td style=";">Eliminated</td><td style="border-top: 1px solid black;;">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Baltimore</td><td style="text-align: right;;">69</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4259</td><td style="text-align: right;;">27</td><td style=";">Eliminated</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Tampa Bay</td><td style="text-align: right;;">66</td><td style="text-align: right;;">96</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4074</td><td style="text-align: right;;">30</td><td style=";">Eliminated</td><td style=";">Eliminated</td></tr></tbody></table>Division 
General MacArthur once said (perhaps speaking of the Red Sox) "We shall return". My question to him is, Does that also go for the Chicago Cubs?

CWatts · Aug 22, 2011

Heh. Though I'm a Sox fan, I do have to tip my hat to the Yankee's as more often then not pull through.

Hope some of that helped with what you were trying to do.

KC2MF · Aug 22, 2011

I completely forgot that I still haven't published ANY of the formulas to back up the numbers that I have. Luckily I have the sheet set for this year so you can see the active numbers (magic or not) in the standings. So here for your viewing pleasure are the current MLB standings for both AL East and Central divisions since both are the only 'competitive' divisional races still in baseball going into today (August 22, 2011).

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">AL East</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">New York (AL)</td><td style="text-align: right;;">77</td><td style="text-align: right;;">48</td><td style="text-align: right;;">0</td><td style="text-align: right;;">125</td><td style="text-align: right;;">37</td><td style="text-align: right;;">0.6160</td><td style=";">Leader</td><td style="text-align: right;border-bottom: 1px solid black;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Boston</td><td style="text-align: right;;">77</td><td style="text-align: right;;">49</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">36</td><td style="text-align: right;;">0.6111</td><td style="text-align: right;;">0.5</td><td style="text-align: right;border-top: 1px solid black;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Tampa Bay</td><td style="text-align: right;;">69</td><td style="text-align: right;;">56</td><td style="text-align: right;;">0</td><td style="text-align: right;;">125</td><td style="text-align: right;;">37</td><td style="text-align: right;;">0.5520</td><td style="text-align: right;;">8</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Toronto</td><td style="text-align: right;;">65</td><td style="text-align: right;;">62</td><td style="text-align: right;;">0</td><td style="text-align: right;;">127</td><td style="text-align: right;;">35</td><td style="text-align: right;;">0.5118</td><td style="text-align: right;;">13</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Baltimore</td><td style="text-align: right;;">47</td><td style="text-align: right;;">77</td><td style="text-align: right;;">0</td><td style="text-align: right;;">124</td><td style="text-align: right;;">38</td><td style="text-align: right;;">0.3790</td><td style="text-align: right;;">29.5</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-top: 1px solid black;;">AL Central</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Detroit</td><td style="text-align: right;;">68</td><td style="text-align: right;;">58</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">36</td><td style="text-align: right;;">0.5397</td><td style=";">Leader</td><td style="text-align: right;border-bottom: 1px solid black;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Cleveland</td><td style="text-align: right;;">62</td><td style="text-align: right;;">61</td><td style="text-align: right;;">0</td><td style="text-align: right;;">123</td><td style="text-align: right;;">39</td><td style="text-align: right;;">0.5041</td><td style="text-align: right;;">4.5</td><td style="text-align: right;border-top: 1px solid black;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Chicago (AL)</td><td style="text-align: right;;">63</td><td style="text-align: right;;">63</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">36</td><td style="text-align: right;;">0.5000</td><td style="text-align: right;;">5</td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Minnesota</td><td style="text-align: right;;">55</td><td style="text-align: right;;">71</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">36</td><td style="text-align: right;;">0.4365</td><td style="text-align: right;;">13</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Kansas City</td><td style="text-align: right;;">52</td><td style="text-align: right;;">76</td><td style="text-align: right;;">0</td><td style="text-align: right;;">128</td><td style="text-align: right;;">34</td><td style="text-align: right;;">0.4063</td><td style="text-align: right;;">17</td><td style="text-align: right;;">19</td></tr></tbody></table>Division <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=SUM(B3:D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=162-E3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=(B3+(D3/2))/(B3+C3+D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=IF(MAX($G$3:$G$7)=G3,"Leader",((MAX($B$3:$B$7)-B3)+(C3-MIN($C$3:$C$7)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=IF(B4+F3-B3+1<1,"Clinched!",B4+(162-E3)-B3+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E10</th><td style="text-align:left">=SUM(B10:D10)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F10</th><td style="text-align:left">=162-E10</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G10</th><td style="text-align:left">=(B10+(D10/2))/(B10+C10+D10)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H10</th><td style="text-align:left">=IF(MAX($G$10:$G$14)=G10,"Leader",((MAX($B$10:$B$14)-B10)+(C10-MIN($C$10:$C$14)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I10</th><td style="text-align:left">=IF(B11+F10-B10+1<1,"Clinched!",B11+(162-E10)-B10+1)</td></tr></tbody></table></td></tr></table> 

Now based on the numbers provided by MLB baseball my numbers are right on with theirs. The reason why the leaders magic number is different from the second place 'tragic' or elimination number is because both teams have played different amounts of games up to that season, meaning there will be a slight differential of sorts of about a couple of games (the largest differential that I've seen so far is 3).

I'm still at a loss of how to make a Wild Card standing sheet. All I know is that it's going to be like a league sheet but that the team with the most wins cannot be in contention for the spot, or perhaps those leading in the division. If anyone, has some ideas of how these should be written if would be great to know. That's all from me for now, I'll definitely have some more calculations and insights tomorrow.

KC2MF · Aug 23, 2011

Got another addition to the divisional sheet. I decided to bite the bullet and add both Wild Card Games Behind and Magic/Elimination number. Here is the American League final results for 2010.

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td><td style="font-weight: bold;;">WC GB</td><td style="font-weight: bold;;">WC M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">AL East</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tampa Bay</td><td style="text-align: right;;">96</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5926</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">New York (AL)</td><td style="text-align: right;;">95</td><td style="text-align: right;;">67</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5864</td><td style="text-align: right;;">1</td><td style="border-top: 1px solid black;;">Eliminated</td><td style=";">Leader</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Boston</td><td style="text-align: right;;">89</td><td style="text-align: right;;">73</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5494</td><td style="text-align: right;;">7</td><td style=";">Eliminated</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Toronto</td><td style="text-align: right;;">85</td><td style="text-align: right;;">77</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5247</td><td style="text-align: right;;">11</td><td style=";">Eliminated</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Baltimore</td><td style="text-align: right;;">66</td><td style="text-align: right;;">96</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4074</td><td style="text-align: right;;">30</td><td style=";">Eliminated</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-top: 1px solid black;;">AL Central</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Minnesota</td><td style="text-align: right;;">94</td><td style="text-align: right;;">68</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5802</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Chicago (AL)</td><td style="text-align: right;;">88</td><td style="text-align: right;;">74</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5432</td><td style="text-align: right;;">6</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Detroit</td><td style="text-align: right;;">81</td><td style="text-align: right;;">81</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5000</td><td style="text-align: right;;">13</td><td style=";">Eliminated</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Clevaland</td><td style="text-align: right;;">69</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4259</td><td style="text-align: right;;">25</td><td style=";">Eliminated</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Kansas City</td><td style="text-align: right;;">67</td><td style="text-align: right;;">95</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4136</td><td style="text-align: right;;">27</td><td style=";">Eliminated</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="font-weight: bold;border-top: 1px solid black;;">AL West</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Texas</td><td style="text-align: right;;">90</td><td style="text-align: right;;">72</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5556</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Oakland</td><td style="text-align: right;;">81</td><td style="text-align: right;;">81</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5000</td><td style="text-align: right;;">9</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Los Angeles (AL)</td><td style="text-align: right;;">80</td><td style="text-align: right;;">82</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4938</td><td style="text-align: right;;">10</td><td style=";">Eliminated</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Seattle</td><td style="text-align: right;;">61</td><td style="text-align: right;;">101</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.3765</td><td style="text-align: right;;">29</td><td style=";">Eliminated</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td></tr></tbody></table>Division <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=SUM(B3:D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=162-E3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=(B3+(D3/2))/(B3+C3+D3)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=IF(MAX($G$3:$G$7)=G3,"Leader",((MAX($B$3:$B$7)-B3)+(C3-MIN($C$3:$C$7)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=IF(B4+F3-B3+1<1,"Clinched!",B4+(162-E3)-B3+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=IF(H$3:H$20="Leader","N/A",IF(LARGE(G$3:G$20,2)=G3,"Leader",((LARGE($B$3:$B$20,2)-B3)+(C3-SMALL($C$3:$C$20,2)))/2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E10</th><td style="text-align:left">=SUM(B10:D10)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F10</th><td style="text-align:left">=162-E10</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G10</th><td style="text-align:left">=(B10+(D10/2))/(B10+C10+D10)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H10</th><td style="text-align:left">=IF(MAX(G$10:G$14)=G10,"Leader",((MAX(B$10:B$14)-B10)+(C10-MIN(C$10:C$14)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I10</th><td style="text-align:left">=IF(B11+F10-B10+1<1,"Clinched!",B11+(162-E10)-B10+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J10</th><td style="text-align:left">=IF(H$3:H$20="Leader","N/A",IF(LARGE(G$3:G$20,2)=G10,"Leader",((LARGE($B$3:$B$20,2)-B10)+(C10-SMALL($C$3:$C$20,2)))/2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E17</th><td style="text-align:left">=SUM(B17:D17)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F17</th><td style="text-align:left">=162-E17</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G17</th><td style="text-align:left">=(B17+(D17/2))/(B17+C17+D17)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H17</th><td style="text-align:left">=IF(MAX(G$17:G$20)=G17,"Leader",((MAX(B$17:B$20)-B17)+(C17-MIN(C$17:C$20)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I17</th><td style="text-align:left">=IF(B18+F17-B17+1<1,"Clinched!",B18+(162-E17)-B17+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J17</th><td style="text-align:left">=IF(H$3:H$20="Leader","N/A",IF(LARGE(G$3:G$20,2)=G17,"Leader",((LARGE($B$3:$B$20,2)-B17)+(C17-SMALL($C$3:$C$20,2)))/2))</td></tr></tbody></table></td></tr></table> 
The Games Behind took some imagination but feel without much trouble but a bug showed up that I patched up for now. The Magic number I have no idea at the moment how to write up. The bug in the GB equation is that on occasion (more frequently in the American League) the wild card is stronger then some (or most) divisional leaders, an example is AL Wild Card record vs. the NL Wild Card from last year. Note the difference in equation with the AL Wild Card GB above with the NL Wild Card GB below.

Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">LG/Div/Team</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">GP</td><td style="font-weight: bold;;">GL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">GB</td><td style="font-weight: bold;;">M/E #</td><td style="font-weight: bold;;">WC GB</td><td style="font-weight: bold;;">WC M/E #</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="font-weight: bold;border-top: 1px solid black;;">NL East</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Philedelphia</td><td style="text-align: right;;">97</td><td style="text-align: right;;">65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5988</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Atlanta</td><td style="text-align: right;;">91</td><td style="text-align: right;;">71</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5617</td><td style="text-align: right;;">6</td><td style="border-top: 1px solid black;;">Eliminated</td><td style=";">Leader</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Florida</td><td style="text-align: right;;">80</td><td style="text-align: right;;">82</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4938</td><td style="text-align: right;;">17</td><td style=";">Eliminated</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">New York (NL)</td><td style="text-align: right;;">79</td><td style="text-align: right;;">83</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4877</td><td style="text-align: right;;">18</td><td style=";">Eliminated</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Washington</td><td style="text-align: right;;">69</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4259</td><td style="text-align: right;;">28</td><td style=";">Eliminated</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="font-weight: bold;border-top: 1px solid black;;">NL Central</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">Cincinnati</td><td style="text-align: right;;">91</td><td style="text-align: right;;">71</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5617</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">St. Louis</td><td style="text-align: right;;">86</td><td style="text-align: right;;">76</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5309</td><td style="text-align: right;;">5</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">Milwaukee</td><td style="text-align: right;;">77</td><td style="text-align: right;;">85</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4753</td><td style="text-align: right;;">14</td><td style=";">Eliminated</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">Houston</td><td style="text-align: right;;">76</td><td style="text-align: right;;">86</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4691</td><td style="text-align: right;;">15</td><td style=";">Eliminated</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">Chicago (NL)</td><td style="text-align: right;;">75</td><td style="text-align: right;;">87</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4630</td><td style="text-align: right;;">16</td><td style=";">Eliminated</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">Pittsburgh</td><td style="text-align: right;;">57</td><td style="text-align: right;;">105</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.3519</td><td style="text-align: right;;">34</td><td style=";">Eliminated</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="font-weight: bold;border-top: 1px solid black;;">NL West</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">San Francisco</td><td style="text-align: right;;">92</td><td style="text-align: right;;">70</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5679</td><td style=";">Leader</td><td style="border-bottom: 1px solid black;;">Clinched!</td><td style=";">N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">San Diego</td><td style="text-align: right;;">90</td><td style="text-align: right;;">72</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5556</td><td style="text-align: right;;">2</td><td style="border-top: 1px solid black;;">Eliminated</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">Colorado</td><td style="text-align: right;;">83</td><td style="text-align: right;;">79</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.5123</td><td style="text-align: right;;">9</td><td style=";">Eliminated</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";">Los Angeles (NL)</td><td style="text-align: right;;">80</td><td style="text-align: right;;">82</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4938</td><td style="text-align: right;;">12</td><td style=";">Eliminated</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";">Arizona</td><td style="text-align: right;;">65</td><td style="text-align: right;;">97</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.4012</td><td style="text-align: right;;">27</td><td style=";">Eliminated</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td></tr></tbody></table>Division <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E23</th><td style="text-align:left">=SUM(B23:D23)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F23</th><td style="text-align:left">=162-E23</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G23</th><td style="text-align:left">=(B23+(D23/2))/(B23+C23+D23)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H23</th><td style="text-align:left">=IF(MAX(G$23:G$27)=G23,"Leader",((MAX(B$23:B$27)-B23)+(C23-MIN(C$23:C$27)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I23</th><td style="text-align:left">=IF(B24+F23-B23+1<1,"Clinched!",B24+(162-E23)-B23+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J23</th><td style="text-align:left">=IF(H$23:H$42="Leader","N/A",IF(LARGE(G$23:G$42,4)=G23,"Leader",((LARGE($B$23:$B$42,4)-B23)+(C23-SMALL($C$23:$C$42,4)))/2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E30</th><td style="text-align:left">=SUM(B30:D30)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F30</th><td style="text-align:left">=162-E30</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G30</th><td style="text-align:left">=(B30+(D30/2))/(B30+C30+D30)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H30</th><td style="text-align:left">=IF(MAX(G$30:G$35)=G30,"Leader",((MAX(B$30:B$35)-B30)+(C30-MIN(C$30:C$35)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I30</th><td style="text-align:left">=IF(B31+F30-B30+1<1,"Clinched!",B31+(162-E30)-B30+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J30</th><td style="text-align:left">=IF(H$23:H$42="Leader","N/A",IF(LARGE(G$23:G$42,4)=G30,"Leader",((LARGE($B$23:$B$42,4)-B30)+(C30-SMALL($C$23:$C$42,4)))/2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E38</th><td style="text-align:left">=SUM(B38:D38)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F38</th><td style="text-align:left">=162-E38</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G38</th><td style="text-align:left">=(B38+(D38/2))/(B38+C38+D38)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H38</th><td style="text-align:left">=IF(MAX(G$38:G$42)=G38,"Leader",((MAX(B$38:B$42)-B38)+(C38-MIN(C$38:C$42)))/2)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I38</th><td style="text-align:left">=IF(B39+F38-B38+1<1,"Clinched!",B39+(162-E38)-B38+1)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J38</th><td style="text-align:left">=IF(H$23:H$42="Leader","N/A",IF(LARGE(G$23:G$42,4)=G38,"Leader",((LARGE($B$23:$B$42,4)-B38)+(C38-SMALL($C$23:$C$42,4)))/2))</td></tr></tbody></table></td></tr></table> 
Anyway, I'm still in the dark about what the Wild Card Standings Sheet will look like, or what the real fix will be for this WC GB formula. The Magic Number for the Wild Card is probably going to be a harder climb to solve. Hopefully before September is out, I'll be putting the finishing touches on the Playoff depth chart.

Tying team name to team record:Completely Stuck!

KC2MF

Board Regular

Excel Facts

CWatts

Well-known Member

KC2MF

Board Regular

CWatts

Well-known Member

KC2MF

Board Regular

KC2MF

Board Regular

Similar threads

Forum statistics

Share this page

Tying team name to team record:Completely Stuck!

KC2MF

Board Regular

Excel Facts

CWatts

Well-known Member

KC2MF

Board Regular

CWatts

Well-known Member

KC2MF

Board Regular

KC2MF

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock