2012-366 Day 108 – Fun with Excel

Someone sent me a question on making a schedule/standings in Excel and I spent a bit too long on the answer. So, rather than only sharing with one person, I’ll share with seven (or so, we’ll go with the average). Here’s the sample file for you to play with too: standings.

Columns: Week, Game #, Team, Score, Pt Diff, Games Won
Week is just the label for the week on the schedule. Each week has the games going down vertically, with as much space as you like between them and between weeks.
Game # was just used for the softball schedule (1, 2, or 3) to indicate game time.
Team is obviously the team name. The important part here is to always use the same team name for the same team throughout the schedule, as we’ll be referencing it later. We had the home team in the first row and visitor in the second, but either way will work.
Score is where the score is placed, each team’s score on the appropriate row.
Pt Diff automatically calculates the Run Differential with the excel formula =D2-D3 for the first team and =D3-D2 for the second team (obviously this will change for each team going down, but you can copy and paste the formula or drag down for a fill and it will automatically adjust the rows for you).
Games Won automatically calculates who won or lost and puts a 1 next to the winner and 0 next to the loser (we’ll use this to tally up the standings later). The formula here is =IF(D2>D3,1,0) for the first row and =IF(D3-D2,1,0) for the second row. Again you can copy and paste or fill down.

This seems a lot more complicated than it is, as it is mostly a bunch of set up. Everything is automated once it’s in place.

Repeat down for the entire schedule.

The last thing is the actual standings table. The only thing you’ll have to do manually is sort it once you’ve put in new information. The standings table has 4 columns (5 and some adjustments if you allow ties): Team, W, L, Pt Diff
Team is the same team name used above, just list them down, each getting their own row.
W is the number of wins and is calculated for each team by the excel formula =SUMIF(C$2:F$80,A85,F$2:F$81) which basically takes the whole schedule (above), looks for the current team name (A85 or wherever your the team name for the standing table ends up, you could also hard code this as Team 1 or whatever), and then adds up the number of wins in the Games Won column that match that team name.
L is the losses which is found by taking the number of games played (from a fixed cell at the bottom of the sheet, in this case $B$98) and subtracting the number of wins from that. If you have a schedule where teams play on different days, this may need to be hard coded for each team.
Pt Diff keeps track of season long point differential using the same mechanism as wins. Excel formula =SUMIF(C$2:E$80,A85,E$2:E$80)

I’ve included a sample worksheet for you to play around with (a currentish version of Microsoft Excel required), hopefully it makes everything clear. Also, it looks like I automated the Games Played section, you can just change that to a flat number if it works better.

Weight: 231 Loss: 9 lbs – Running Yearly Mileage: 101.2 miles
Fitocracy Level: 18 (38520 points, 1330 to next level) – ID: disciplev1
Soccer – Last Game: W, 13-5 (Record: 2-4) Next Game: 4/22 – 7:30 pm