Wins + Losses for Sports Teams at Different Points

alex58392

New Member
Joined
Jun 2, 2019
Messages
2
Hi all,
First time posting and looking for some help regarding a set of sports statistics. I am looking to analyze the effect of home court advantage in the 2018-2019 season and wanted to be able to control for how many wins each team has at a certain time in the season. I currently have a set of 1230 games. Each row has the score of the game for the away team and the home team along with neighboring columns stating the identity of the home team and the away team. I was wondering if it is possible to create a formula that can analyze the scores of each game and which team they correspond to so as to calculate a sort of "running wins and losses total" that borders each game played. For example, if the Golden State Warriors have played 6 games and won 5 of them, I would like the row indicating their seventh game to have 5 wins next to it so I can attempt to control for individual team performance (a better team wins more games) in regressing for home court advantage. Thank you in advance for any help, please let me know if you have any questions. Thanks!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
please you post a small sample and desired output of the data here
 

alex58392

New Member
Joined
Jun 2, 2019
Messages
2
ROAD TEAMHOME TEAMAway Points ScoredHome Points ScoredDifferenceAway Days RestHome Days RestAttend.Away 3IN4-B2BAway B2BAway 3IN4Away 1Away 2Away 3MoreHome 3IN4-B2BHome B2BHome 3IN4Home 1Home 2Home 3More
10/16/18Philadelphia 76ersBoston Celtics87105183More3More18624000001000001
10/16/18Oklahoma City ThunderGolden State Warriors10010883More3More19596000001000001
10/17/18Milwaukee BucksCharlotte Hornets113112-13More3More17889000001000001
10/17/18Brooklyn NetsDetroit Pistons10010333More3More20332000001000001
10/17/18New Orleans PelicansHouston Rockets131112-193More3More18055000001000001
10/17/18Memphis GrizzliesIndiana Pacers83111283More3More17923000001000001
10/17/18Denver NuggetsLos Angeles Clippers10798-93More3More19068000001000001
10/17/18Atlanta HawksNew York Knicks107126193More3More18249000001000001
10/17/18Miami HeatOrlando Magic10110433More3More19191000001000001
10/17/18Dallas MavericksPhoenix Suns100121213More3More18055000001000001
10/17/18Utah JazzSacramento Kings123117-63More3More17583000001000001
10/17/18Minnesota TimberwolvesSan Antonio Spurs10811243More3More18354000001000001
10/17/18Cleveland CavaliersToronto Raptors104116123More3More19915000001000001
10/18/18Chicago BullsPhiladelphia 76ers108127193More120302000001000100
10/18/18Los Angeles LakersPortland Trail Blazers11912893More3More19996000001000001
10/18/18Miami HeatWashington Wizards113112-1B2B3More20409010000000001
10/19/18New York KnicksBrooklyn Nets10510721117732000100000100
10/19/18Oklahoma City ThunderLos Angeles Clippers92108162114816000010000100
10/19/18Atlanta HawksMemphis Grizzlies117131141117019000100000100
10/19/18Indiana PacersMilwaukee Bucks101118171117341000100000100
10/19/18Cleveland CavaliersMinnesota Timberwolves12313181118978000100000100
10/19/18Sacramento KingsNew Orleans Pelicans129149201118337000100000100
10/19/18Charlotte HornetsOrlando Magic12088-321117668000100000100
10/19/18Boston CelticsToronto Raptors101113122119800000010000100
10/19/18Golden State WarriorsUtah Jazz124123-12118306000010000100
10/20/18Detroit PistonsChicago Bulls118116-22121289000010000100
10/20/18Minnesota TimberwolvesDallas Mavericks13614043IN4-B2B220205100000000010
10/20/18Phoenix SunsDenver Nuggets91119282219592000010000010
10/20/18Brooklyn NetsIndiana Pacers103132293IN4-B2B3IN4-B2B17007100000100000
10/20/18Houston RocketsLos Angeles Lakers124115-92118997000010000100
10/20/18Charlotte HornetsMiami Heat113112-13IN4-B2B3IN419600100000001000
10/20/18Boston CelticsNew York Knicks112101-11B2B3IN4-B2B19427010000100000
10/20/18Orlando MagicPhiladelphia 76ers11511613IN4-B2B120300100000000100
10/20/18San Antonio SpursPortland Trail Blazers108121132119461000010000100
10/20/18Toronto RaptorsWashington Wizards117113-43IN4-B2B116185100000000100
10/21/18Atlanta HawksCleveland Cavaliers133111-221119432000100000100
10/21/18Golden State WarriorsDenver Nuggets9810021B2B19520000100010000
10/21/18Houston RocketsLos Angeles Clippers1121153B2B116149010000000100
10/21/18Sacramento KingsOklahoma City Thunder131120-111118203000100000100

<colgroup><col width="65" span="21" style="width: 65pt;"></colgroup><tbody>
</tbody>

Here is a subset of the data, I would like to create two new columns, one containing the number of cumulative wins for the specific home team playing and another for the away team. If it is easier, I can reorient the data so that it is in alphabetical order by data (to group the same home and away teams together). Please let me know if this is better.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
have a look if this is what you're after

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>V</th><th>W</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ROAD TEAM</td><td style=";">HOME TEAM</td><td style=";">Away Points Scored</td><td style=";">Home Points Scored</td><td style=";">Difference</td><td style=";">Away Days Rest</td><td style=";">HOME wins</td><td style=";">AWAY wins</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10/16/18</td><td style=";">Philadelphia 76ers</td><td style=";">Boston Celtics</td><td style="text-align: right;;">87</td><td style="text-align: right;;">105</td><td style="text-align: right;;">18</td><td style="text-align: right;background-color: #C6E0B4;;">0</td><td style="text-align: right;background-color: #C6E0B4;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10/16/18</td><td style=";">Oklahoma City Thunder</td><td style=";">Golden State Warriors</td><td style="text-align: right;;">100</td><td style="text-align: right;;">108</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10/17/18</td><td style=";">Milwaukee Bucks</td><td style=";">Charlotte Hornets</td><td style="text-align: right;;">113</td><td style="text-align: right;;">112</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">10/17/18</td><td style=";">Brooklyn Nets</td><td style=";">Detroit Pistons</td><td style="text-align: right;;">100</td><td style="text-align: right;;">103</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">10/17/18</td><td style=";">New Orleans Pelicans</td><td style=";">Houston Rockets</td><td style="text-align: right;;">131</td><td style="text-align: right;;">112</td><td style="text-align: right;;">-19</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10/17/18</td><td style=";">Memphis Grizzlies</td><td style=";">Indiana Pacers</td><td style="text-align: right;;">83</td><td style="text-align: right;;">111</td><td style="text-align: right;;">28</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10/17/18</td><td style=";">Denver Nuggets</td><td style=";">Los Angeles Clippers</td><td style="text-align: right;;">107</td><td style="text-align: right;;">98</td><td style="text-align: right;;">-9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">10/17/18</td><td style=";">Atlanta Hawks</td><td style=";">New York Knicks</td><td style="text-align: right;;">107</td><td style="text-align: right;;">126</td><td style="text-align: right;;">19</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">10/17/18</td><td style=";">Miami Heat</td><td style=";">Orlando Magic</td><td style="text-align: right;;">101</td><td style="text-align: right;;">104</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">10/17/18</td><td style=";">Dallas Mavericks</td><td style=";">Phoenix Suns</td><td style="text-align: right;;">100</td><td style="text-align: right;;">121</td><td style="text-align: right;;">21</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">10/17/18</td><td style=";">Utah Jazz</td><td style=";">Sacramento Kings</td><td style="text-align: right;;">123</td><td style="text-align: right;;">117</td><td style="text-align: right;;">-6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">10/17/18</td><td style=";">Minnesota Timberwolves</td><td style=";">San Antonio Spurs</td><td style="text-align: right;;">108</td><td style="text-align: right;;">112</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">10/17/18</td><td style=";">Cleveland Cavaliers</td><td style=";">Toronto Raptors</td><td style="text-align: right;;">104</td><td style="text-align: right;;">116</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">10/18/18</td><td style=";">Chicago Bulls</td><td style=";">Philadelphia 76ers</td><td style="text-align: right;;">108</td><td style="text-align: right;;">127</td><td style="text-align: right;;">19</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">10/18/18</td><td style=";">Los Angeles Lakers</td><td style=";">Portland Trail Blazers</td><td style="text-align: right;;">119</td><td style="text-align: right;;">128</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">10/18/18</td><td style=";">Miami Heat</td><td style=";">Washington Wizards</td><td style="text-align: right;;">113</td><td style="text-align: right;;">112</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">10/19/18</td><td style=";">New York Knicks</td><td style=";">Brooklyn Nets</td><td style="text-align: right;;">105</td><td style="text-align: right;;">107</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">10/19/18</td><td style=";">Oklahoma City Thunder</td><td style=";">Los Angeles Clippers</td><td style="text-align: right;;">92</td><td style="text-align: right;;">108</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">10/19/18</td><td style=";">Atlanta Hawks</td><td style=";">Memphis Grizzlies</td><td style="text-align: right;;">117</td><td style="text-align: right;;">131</td><td style="text-align: right;;">14</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">10/19/18</td><td style=";">Indiana Pacers</td><td style=";">Milwaukee Bucks</td><td style="text-align: right;;">101</td><td style="text-align: right;;">118</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">10/19/18</td><td style=";">Cleveland Cavaliers</td><td style=";">Minnesota Timberwolves</td><td style="text-align: right;;">123</td><td style="text-align: right;;">131</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">10/19/18</td><td style=";">Sacramento Kings</td><td style=";">New Orleans Pelicans</td><td style="text-align: right;;">129</td><td style="text-align: right;;">149</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">10/19/18</td><td style=";">Charlotte Hornets</td><td style=";">Orlando Magic</td><td style="text-align: right;;">120</td><td style="text-align: right;;">88</td><td style="text-align: right;;">-32</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">10/19/18</td><td style=";">Boston Celtics</td><td style=";">Toronto Raptors</td><td style="text-align: right;;">101</td><td style="text-align: right;;">113</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">10/19/18</td><td style=";">Golden State Warriors</td><td style=";">Utah Jazz</td><td style="text-align: right;;">124</td><td style="text-align: right;;">123</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">10/20/18</td><td style=";">Detroit Pistons</td><td style=";">Chicago Bulls</td><td style="text-align: right;;">118</td><td style="text-align: right;;">116</td><td style="text-align: right;;">-2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">10/20/18</td><td style=";">Minnesota Timberwolves</td><td style=";">Dallas Mavericks</td><td style="text-align: right;;">136</td><td style="text-align: right;;">140</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">10/20/18</td><td style=";">Phoenix Suns</td><td style=";">Denver Nuggets</td><td style="text-align: right;;">91</td><td style="text-align: right;;">119</td><td style="text-align: right;;">28</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">10/20/18</td><td style=";">Brooklyn Nets</td><td style=";">Indiana Pacers</td><td style="text-align: right;;">103</td><td style="text-align: right;;">132</td><td style="text-align: right;;">29</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">10/20/18</td><td style=";">Houston Rockets</td><td style=";">Los Angeles Lakers</td><td style="text-align: right;;">124</td><td style="text-align: right;;">115</td><td style="text-align: right;;">-9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style=";">10/20/18</td><td style=";">Charlotte Hornets</td><td style=";">Miami Heat</td><td style="text-align: right;;">113</td><td style="text-align: right;;">112</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style=";">10/20/18</td><td style=";">Boston Celtics</td><td style=";">New York Knicks</td><td style="text-align: right;;">112</td><td style="text-align: right;;">101</td><td style="text-align: right;;">-11</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style=";">10/20/18</td><td style=";">Orlando Magic</td><td style=";">Philadelphia 76ers</td><td style="text-align: right;;">115</td><td style="text-align: right;;">116</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style=";">10/20/18</td><td style=";">San Antonio Spurs</td><td style=";">Portland Trail Blazers</td><td style="text-align: right;;">108</td><td style="text-align: right;;">121</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style=";">10/20/18</td><td style=";">Toronto Raptors</td><td style=";">Washington Wizards</td><td style="text-align: right;;">117</td><td style="text-align: right;;">113</td><td style="text-align: right;;">-4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style=";">10/21/18</td><td style=";">Atlanta Hawks</td><td style=";">Cleveland Cavaliers</td><td style="text-align: right;;">133</td><td style="text-align: right;;">111</td><td style="text-align: right;;">-22</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style=";">10/21/18</td><td style=";">Golden State Warriors</td><td style=";">Denver Nuggets</td><td style="text-align: right;;">98</td><td style="text-align: right;;">100</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style=";">10/21/18</td><td style=";">Houston Rockets</td><td style=";">Los Angeles Clippers</td><td style="text-align: right;;">112</td><td style="text-align: right;;">115</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">40</td><td style=";">10/21/18</td><td style=";">Sacramento Kings</td><td style=";">Oklahoma City Thunder</td><td style="text-align: right;;">131</td><td style="text-align: right;;">120</td><td style="text-align: right;;">-11</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">V2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$2:B2=B2</font>),--(<font color="Red">$F$2:F2<0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$C$2:C2=C2</font>),--(<font color="Red">$F$2:F2>0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,141,400
Messages
5,706,212
Members
421,432
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top