I have a spreadsheet that dynamically calculates a team's expected winning percentage based on the strength of its remaining opposition. When I update the won-lost records for each team in the league, each team's strength is automatically recalculated.
My problem lies with having to manually alter the arrays every day. Every time a team plays a game, then there are fewer games left to play.
I use one table to record the wins and losses for each team. That data is used to calculate the log5 number for that team. The table aslo includes the game-by-game opponents for the team:
Team W L log5 OPP1 OPP2 OPP... OPP162
ANA 88 55 .800 CLE CLE ... NYA
There are 30 teams, but I'm just showing one to save space.
I set up the next table as a vlookup to substitute the log5 number for each opponent that a team has
Team OPP1 OPP2 OPP... OPP162
ANA .405 .405 ... .840
I use the average of the log5 numbers for OPP1, OPP2,...,OPP162 [SUM(E30:FJ30)/COUNT(E30:FJ30)] as the basis for determing the expected won-lost percentage, but after a game is played the remaining schedule is OPP2,OPP3,...OPP162 [SUM(F30:FJ30)/COUNT(F30:FJ30)]and the array has to be adjusted accordingly. Because every team does not always play every day, and they don't necessarily play the same number of games on a given day, I can't simply change one formula and copy it down.
Is there some way that I can handle this more easily? As an example if all three of these teams played today, I would need to change two formulas for each team
A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AB67:AT67) =AVERAGE(AB67:AT67)
In C and D, the ranges are dynamic, and after today's games (assuming that the team played) would need to be updated to:
A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AC67:AT67) =AVERAGE(AC67:AT67)
This is very tedious to do for thirty teams, and very much open to error.
I hope this is clear, thanks,
Cliff
My problem lies with having to manually alter the arrays every day. Every time a team plays a game, then there are fewer games left to play.
I use one table to record the wins and losses for each team. That data is used to calculate the log5 number for that team. The table aslo includes the game-by-game opponents for the team:
Team W L log5 OPP1 OPP2 OPP... OPP162
ANA 88 55 .800 CLE CLE ... NYA
There are 30 teams, but I'm just showing one to save space.
I set up the next table as a vlookup to substitute the log5 number for each opponent that a team has
Team OPP1 OPP2 OPP... OPP162
ANA .405 .405 ... .840
I use the average of the log5 numbers for OPP1, OPP2,...,OPP162 [SUM(E30:FJ30)/COUNT(E30:FJ30)] as the basis for determing the expected won-lost percentage, but after a game is played the remaining schedule is OPP2,OPP3,...OPP162 [SUM(F30:FJ30)/COUNT(F30:FJ30)]and the array has to be adjusted accordingly. Because every team does not always play every day, and they don't necessarily play the same number of games on a given day, I can't simply change one formula and copy it down.
Is there some way that I can handle this more easily? As an example if all three of these teams played today, I would need to change two formulas for each team
A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AB67:AT67) =AVERAGE(AB67:AT67)
In C and D, the ranges are dynamic, and after today's games (assuming that the team played) would need to be updated to:
A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AC67:AT67) =AVERAGE(AC67:AT67)
This is very tedious to do for thirty teams, and very much open to error.
I hope this is clear, thanks,
Cliff