Excel 2007 : help with stat tracker

**aeb** · 03-01-2012, 11:18 PM

Here's the deal. I've created a score tracker for a hockey game that my friends and I enjoy playing. I've been able to calculate most statistics I want, though I'm having trouble with coming up with a goals against stat. The basic layout of my sheet is as follows: a1 is a letter identifier for the away player, b1 is the team they've chosen, c1 is the goals scored for their team. a2 is the letter identifier for the home player, b2 is their team choice, and c1 is their goals scored. Row 3 is then left blank. The next game repeats in the same fashion beginning in A4. As I mentioned, the stat I'm having trouble with is goals against. I don't know how to associate a value with the letter identifier for the opposite player, especially if the opposite player's position is not fixed either above or below, as the winning team can be either home or away. The other question I had was with regard to another stat, shutouts. Currently, I have a designator in column E to designate a shutout (column d is used to designate wins), though if there's a way to work out a formula like from my first question, I bet it could be tweaked to account for shutouts and even wins. Please refer to attached book. Any help is much appreciated. Thanks!

**DGagnon** · 03-01-2012, 11:31 PM

i made a couple cahnges to your file, i added a bit of logic ot the helper colums, as well as added one additional. i also added a player key column to help if you ever add a player. but this should do what you are looking for.