Help with Sports Schedule/Standings Management

**Rhinorocka14** · 06-21-2012, 12:15 AM

Hey everyone,

I have a spreadsheet that basically manages the schedule of a hockey tournament. What I'd like to accomplish is 2 basic things (all in different sheets, same workbook):

1.) Schedule with scores
2.) Standings that update based on the inputted scores in the schedule

The schedule is basically all set, but I want to know how to manage the standings page.

The standings will have 4 different divisions (North, East, South, West) each with 4 teams (teams identified by numbers 1-16).

Each win is 2 points, a tie is 1 point, and a loss is 0 points. I would like to have a standings page that will take the scores from the schedule page and, with a formula, update all the information on the standings page. I'm okay with creating a macro to automatically sort, but I need help with the formula part of determining what happens on the standings page with a win, loss, and a tie.

My biggest problem is this: I'm familiar with basic IF functions, but I can't for the life of me figure out how to arrange everything in a simple schedule format that would translate well to the standings page.

For example, I have split up each team's schedule based on the full schedule. So one sheet will have the master schedule (which I want the guys running the tournament to input final scores and THAT'S IT!). The other sheet has the list of teams and their games. One column is their score for Team 1 Game 1, and the other column is Opponent Score. The result in the third column is just an IF function---IF(ISBLANK(A1),"",(IF(A1>A2,"Win",(IF(A1=A2, "Tie", "Loss")))))

My problem is that I have no way to draw from this information and update the standings page. I'd like it so that it does the math automatically, where if a result =Win, then add a 1 to the win column. Same goes for everything else. The Points column in the standings page is a simple formula that takes the win column * 2, and tie column * 1 and adds it together.

Can anyone help me with this? I've tried looking at FIFA templates, since those seem to be the only ones available online. However, they don't factor in ties and what not.

Any help is much appreciated at this point!

**MarvinP** · 06-21-2012, 01:15 AM

Hi Rhinorocka and welcome to the forum,

Find attached what I think you want. I've created some very fake data in columns A to C. You would need to put in each team and the region and points. I'd suggest a date or round column also to the data. Then using Pivot Tables with the Region Filter you can easily show who is ahead. See the attached. You can also sort by sum of scores to move better teams up the pivot table list.

**Rhinorocka14** · 06-22-2012, 11:48 AM

Hey Marvin,

Thanks for the reply! Much appreciated. Glad to be part of a forum that is already helpful!

I've attached a revised version of the file you sent me. I included a couple of questions as well.

What I'd like to do is have a master schedule that basically lists games from #1-90 (we have 2 tournaments but they're both played at the same venue so we just number them chronologically.)

If you can help me figure out a way to link up the two different sheets so I can use the Pivot Table to sort the standings that'd be great. The only thing is that I'd like the Pivot Table to have Wins, Losses, Ties, Points, Goals For, and Goals Against as categories. That way, parents can see the details of the standings, and why one team is first place and the other team is second place (helps with tie-breakers and what not).

I'm thinking I need to make a formula that says if score 1 is greater than score 2 for game 1, give team A a "Win" and team B a "loss". Then, on the next sheet, it would have a formula saying, if Team A's result is a Win, add a Win to the Win column, or something like that?

Anything you can do will help tremendously! Much appreciation ahead of time sir!

Regards,
James