Averageif/Averageifs help calculating strength of schedule

[sAustinPower]

Hey guys,

So I'm stuck on this one, and it's late, and I've been trying too many things with no results and would love some help. I'm trying to calculate a strength of schedule based on win % for multiple teams based games played and then also calculate a recent SOS based on games played only last week. I believe if I can get the first SOS figured out, I can get it to update based on week #. So I have a Games sheet setup where you enter the Week #, Home Team, Home Score, Away Score and Away Team. Then I calculate the winner, loser, home margin and away margin. See the below table (I really hope this formats nicely!):


Week # Home Team Home Score Away Score Away Team Winner Loser Home Margin Away Margin
1 Team 1 15 14 Team 2 Team 1 Team 2 1 -1
1 Team 1 15 11 Team 3 Team 1 Team 3 4 -4
1 Team 2 15 9 Team 3 Team 2 Team 3 6 -6



Then on the next sheet you need to enter the same Team Name and I calculate total wins, losses, final margin across all games and win %. These are working no problem. But now I'm trying to calculate SOS based on the average Win % of any opponents you have played:


Team Name Wins Losses Final Margin Final SOS Win % Last Week
Team 1 2 0 5 0 1 1
Team 2 1 1 5 0 0.5 1
Team 3 0 2 -10 0 0 1


So for example, let's look at Team 1. They played both teams 2 and 3. Team 2 has a Win % of .5. Team 3 has a Win % of 0. As a result, Team 1's SOS should be .25, the average of the Win % of teams they have played. Team 2 would have a .5 SOS and Team 3 would have a .75 SOS.

Anyone know how to calculate this using formulas? I've been trying vlookups, indexes, averageifs, and I don't remember what else and really haven't come close yet. Any help here, is really appreciated.

Thanks!

- Austin