I am currently putting together an RPI for our league but am having difficulty calculating the strength of schedule (SOS1) and the opponents' strength of schedule (SOS2). The calculations for the SOS2 is complex in and of itself. SOS1 is the average of the win/loss%s of a particular team's opponents (Sum of win/loss% of opponents/# of opponents). SOS2 is the average of the win/loss%s of a team's opponents' opponents; however, it must remove from the calculation of win/loss% any games in which the team you are calculating the RPI for played.
I have a base table in SheetA that I am working off of that has columns A thru G (A-Date, B-Home Team, C-Away Team, D-Winner Score, E-Loser Score, F-Winner, G-Loser). I can't use any function that requires a sorting of the table because teams show up in two columns (B & C). I can't separate teams into a single column because Team A eventually plays Team B.
I have a second sheet (SheetB) that lists the teams in the league (121 total) and has columns for Wins, Losses, Win/Loss %, SOS1, SOS2, and RPI.
So my first challenge is figuring out how to create a list of opponents for a single team, and then using this list to sum their win/loss%s and dividing that number by the total number of opponents. If I can get the list of a team's opponents, I think I can do the averaging fairly easily.
My second, and far more daunting challenge, is then to do the exact calculation above but for more than one team (up to 25) and removing any instances where Team A was the opponent all in one formula.
I am fearing that this may require at least Access, but I am hoping that someone has an idea of how to do this in Excel.
I have a base table in SheetA that I am working off of that has columns A thru G (A-Date, B-Home Team, C-Away Team, D-Winner Score, E-Loser Score, F-Winner, G-Loser). I can't use any function that requires a sorting of the table because teams show up in two columns (B & C). I can't separate teams into a single column because Team A eventually plays Team B.
I have a second sheet (SheetB) that lists the teams in the league (121 total) and has columns for Wins, Losses, Win/Loss %, SOS1, SOS2, and RPI.
So my first challenge is figuring out how to create a list of opponents for a single team, and then using this list to sum their win/loss%s and dividing that number by the total number of opponents. If I can get the list of a team's opponents, I think I can do the averaging fairly easily.
My second, and far more daunting challenge, is then to do the exact calculation above but for more than one team (up to 25) and removing any instances where Team A was the opponent all in one formula.
I am fearing that this may require at least Access, but I am hoping that someone has an idea of how to do this in Excel.
