Wins + Losses for Sports Teams at Different Points

alex58392

New Member
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
please you post a small sample and desired output of the data here

alex58392

New Member
 ROAD TEAM HOME TEAM Away Points Scored Home Points Scored Difference Away Days Rest Home Days Rest Attend. Away 3IN4-B2B Away B2B Away 3IN4 Away 1 Away 2 Away 3More Home 3IN4-B2B Home B2B Home 3IN4 Home 1 Home 2 Home 3More 10/16/18 Philadelphia 76ers Boston Celtics 87 105 18 3More 3More 18624 0 0 0 0 0 1 0 0 0 0 0 1 10/16/18 Oklahoma City Thunder Golden State Warriors 100 108 8 3More 3More 19596 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Milwaukee Bucks Charlotte Hornets 113 112 -1 3More 3More 17889 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Brooklyn Nets Detroit Pistons 100 103 3 3More 3More 20332 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 New Orleans Pelicans Houston Rockets 131 112 -19 3More 3More 18055 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Memphis Grizzlies Indiana Pacers 83 111 28 3More 3More 17923 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Denver Nuggets Los Angeles Clippers 107 98 -9 3More 3More 19068 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Atlanta Hawks New York Knicks 107 126 19 3More 3More 18249 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Miami Heat Orlando Magic 101 104 3 3More 3More 19191 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Dallas Mavericks Phoenix Suns 100 121 21 3More 3More 18055 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Utah Jazz Sacramento Kings 123 117 -6 3More 3More 17583 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Minnesota Timberwolves San Antonio Spurs 108 112 4 3More 3More 18354 0 0 0 0 0 1 0 0 0 0 0 1 10/17/18 Cleveland Cavaliers Toronto Raptors 104 116 12 3More 3More 19915 0 0 0 0 0 1 0 0 0 0 0 1 10/18/18 Chicago Bulls Philadelphia 76ers 108 127 19 3More 1 20302 0 0 0 0 0 1 0 0 0 1 0 0 10/18/18 Los Angeles Lakers Portland Trail Blazers 119 128 9 3More 3More 19996 0 0 0 0 0 1 0 0 0 0 0 1 10/18/18 Miami Heat Washington Wizards 113 112 -1 B2B 3More 20409 0 1 0 0 0 0 0 0 0 0 0 1 10/19/18 New York Knicks Brooklyn Nets 105 107 2 1 1 17732 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Oklahoma City Thunder Los Angeles Clippers 92 108 16 2 1 14816 0 0 0 0 1 0 0 0 0 1 0 0 10/19/18 Atlanta Hawks Memphis Grizzlies 117 131 14 1 1 17019 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Indiana Pacers Milwaukee Bucks 101 118 17 1 1 17341 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Cleveland Cavaliers Minnesota Timberwolves 123 131 8 1 1 18978 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Sacramento Kings New Orleans Pelicans 129 149 20 1 1 18337 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Charlotte Hornets Orlando Magic 120 88 -32 1 1 17668 0 0 0 1 0 0 0 0 0 1 0 0 10/19/18 Boston Celtics Toronto Raptors 101 113 12 2 1 19800 0 0 0 0 1 0 0 0 0 1 0 0 10/19/18 Golden State Warriors Utah Jazz 124 123 -1 2 1 18306 0 0 0 0 1 0 0 0 0 1 0 0 10/20/18 Detroit Pistons Chicago Bulls 118 116 -2 2 1 21289 0 0 0 0 1 0 0 0 0 1 0 0 10/20/18 Minnesota Timberwolves Dallas Mavericks 136 140 4 3IN4-B2B 2 20205 1 0 0 0 0 0 0 0 0 0 1 0 10/20/18 Phoenix Suns Denver Nuggets 91 119 28 2 2 19592 0 0 0 0 1 0 0 0 0 0 1 0 10/20/18 Brooklyn Nets Indiana Pacers 103 132 29 3IN4-B2B 3IN4-B2B 17007 1 0 0 0 0 0 1 0 0 0 0 0 10/20/18 Houston Rockets Los Angeles Lakers 124 115 -9 2 1 18997 0 0 0 0 1 0 0 0 0 1 0 0 10/20/18 Charlotte Hornets Miami Heat 113 112 -1 3IN4-B2B 3IN4 19600 1 0 0 0 0 0 0 0 1 0 0 0 10/20/18 Boston Celtics New York Knicks 112 101 -11 B2B 3IN4-B2B 19427 0 1 0 0 0 0 1 0 0 0 0 0 10/20/18 Orlando Magic Philadelphia 76ers 115 116 1 3IN4-B2B 1 20300 1 0 0 0 0 0 0 0 0 1 0 0 10/20/18 San Antonio Spurs Portland Trail Blazers 108 121 13 2 1 19461 0 0 0 0 1 0 0 0 0 1 0 0 10/20/18 Toronto Raptors Washington Wizards 117 113 -4 3IN4-B2B 1 16185 1 0 0 0 0 0 0 0 0 1 0 0 10/21/18 Atlanta Hawks Cleveland Cavaliers 133 111 -22 1 1 19432 0 0 0 1 0 0 0 0 0 1 0 0 10/21/18 Golden State Warriors Denver Nuggets 98 100 2 1 B2B 19520 0 0 0 1 0 0 0 1 0 0 0 0 10/21/18 Houston Rockets Los Angeles Clippers 112 115 3 B2B 1 16149 0 1 0 0 0 0 0 0 0 1 0 0 10/21/18 Sacramento Kings Oklahoma City Thunder 131 120 -11 1 1 18203 0 0 0 1 0 0 0 0 0 1 0 0

<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
have a look if this is what you're after

Replies
1
Views
103
Replies
0
Views
316
Replies
3
Views
361
Replies
5
Views
263
Replies
0
Views
442

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?

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

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