I'm formatting sports standings to be used for all sports (including fantasy) and here's what I have so far.
The formula to determine winning percentage is . . .
=(B3+(D3/2))/(B3+C3+D3) where . .
B3 is the teams wins,
D3 is the teams ties (if there are any),
C3 is the teams losses
and the formula for devising how many games behind the team is . . .
=IF(MAX(B$3:B$7)-B3=0,"Leader",(MAX(B$3:B$7)-B3)+(MIN(C$3:C$7)-C3)*2) where . .
B$3:B$7 is the teams and it's division wins etc.
B3 is the leaders # of wins
C$3:C$7 is the teams and it's divisional opponents losses
I also I know that any wild card will be found with the use of the large formula (I just don't what the equation will look like).
Armed with these two formulas and the records of each team (wins, losses, and ties) placed individually as independent variables, I've created four worksheets. The first is to measure team standings within their proper division, the second to measure the team standings within the larger league, the third is for measuring teams jockeying for a "Wild Card" berth, the fourth is to create a picture of what the playoffs would look like if the season were to end that day.
Here are my issues so far . . .
1) I need to create an all in one formula so as to tie together the teams name with their record (W, L, T, Win%, and GB). Without it I'll have to cut and re-paste the teams name AND record should they fall or rise in the standings. Is this where I should use a list? I'm completely stumped on this one.
2) As you know many professional sports have wild card seeds. I'm wondering if there is a formula I could use that could eliminate teams and their records from being shown in a wild card standing if their already leading their division or the league. The problem is that some divisional leaders lag behind the wild card leader, meaning there could be some chaos on the standings. What would be the best course of action in this case? Can this be done?
Thanks for anyone who can step up to the plate and can help me get this one right.
The formula to determine winning percentage is . . .
=(B3+(D3/2))/(B3+C3+D3) where . .
B3 is the teams wins,
D3 is the teams ties (if there are any),
C3 is the teams losses
and the formula for devising how many games behind the team is . . .
=IF(MAX(B$3:B$7)-B3=0,"Leader",(MAX(B$3:B$7)-B3)+(MIN(C$3:C$7)-C3)*2) where . .
B$3:B$7 is the teams and it's division wins etc.
B3 is the leaders # of wins
C$3:C$7 is the teams and it's divisional opponents losses
I also I know that any wild card will be found with the use of the large formula (I just don't what the equation will look like).
Armed with these two formulas and the records of each team (wins, losses, and ties) placed individually as independent variables, I've created four worksheets. The first is to measure team standings within their proper division, the second to measure the team standings within the larger league, the third is for measuring teams jockeying for a "Wild Card" berth, the fourth is to create a picture of what the playoffs would look like if the season were to end that day.
Here are my issues so far . . .
1) I need to create an all in one formula so as to tie together the teams name with their record (W, L, T, Win%, and GB). Without it I'll have to cut and re-paste the teams name AND record should they fall or rise in the standings. Is this where I should use a list? I'm completely stumped on this one.
2) As you know many professional sports have wild card seeds. I'm wondering if there is a formula I could use that could eliminate teams and their records from being shown in a wild card standing if their already leading their division or the league. The problem is that some divisional leaders lag behind the wild card leader, meaning there could be some chaos on the standings. What would be the best course of action in this case? Can this be done?
Thanks for anyone who can step up to the plate and can help me get this one right.
