Preacherman771
New Member
- Joined
- Jun 15, 2021
- Messages
- 46
- Office Version
- 365
- Platform
- Windows
I am creating a spread sheet which calculates football rankings from various data sheets. I'm currently working on the worksheet which ranks the teams when having equal winning percentage based on tie breaking rules. There are a total of 6 consecutive rules. I have attached the list of teams with their percentage and the first tie breaking rules grouping. I have 2 question: 1) How do I setup the RANK.EQ(xx,xx:xx,0) based on which various teams have the equal winning percentages, e.g., a formula that automatically knows that T28:T31 are equal and then T34:T35 are equal.
Question #2: The first tie-breaking rule is based on best head to head play percentage. I've attached the range which I have set up the rule check range. Again, how would I set up IFERROR(SUM(((0.5*(COUNTIF(xx:xx,"T"))+(COUNTIF(xx:xx,"W"))/SUM((COUNTIF(xx:xx,"W")+COUNTIF(xx:xx,"L")+(COUNTIF(xx:xx,"T"))))))),"---")), formula to calculate the percentage for various teams which have equal percentages in column CF81:CF91, (e.g., T81:T91 Z81:Z91 equal then AF81:AF91 AP81:AP91 equal),
NFL 2021-2022 Standings (Template).xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
6 | Team | All | Div | Conf | |||||||||||||||
7 | 1 | KC | 0.875 | 0.667 | 0.833 | ||||||||||||||
8 | 2 | BUF | 0.813 | 1.000 | 0.833 | ||||||||||||||
9 | 3 | PIT | 0.750 | 0.667 | 0.750 | ||||||||||||||
10 | 4 | BAL | 0.688 | 0.667 | 0.583 | ||||||||||||||
11 | 5 | CLE | 0.688 | 0.500 | 0.583 | ||||||||||||||
12 | 6 | IND | 0.688 | 0.667 | 0.583 | ||||||||||||||
13 | 7 | TEN | 0.688 | 0.833 | 0.667 | ||||||||||||||
14 | 8 | MIA | 0.625 | 0.500 | 0.583 | ||||||||||||||
15 | 9 | LV | 0.500 | 0.667 | 0.500 | ||||||||||||||
16 | # | NE | 0.438 | 0.500 | 0.500 | ||||||||||||||
17 | # | LAC | 0.438 | 0.500 | 0.500 | ||||||||||||||
18 | # | DEN | 0.313 | 0.167 | 0.333 | ||||||||||||||
19 | # | CIN | 0.281 | 0.167 | 0.333 | ||||||||||||||
20 | # | HOU | 0.250 | 0.333 | 0.250 | ||||||||||||||
21 | # | NYJ | 0.125 | 0.000 | 0.083 | ||||||||||||||
22 | # | JAX | 0.063 | 0.167 | 0.083 | ||||||||||||||
23 | |||||||||||||||||||
24 | Rnd 1 | Team | Team | Pct | H to H GAMES (1) | ||||||||||||||
25 | KC | --- | --- | -- | --- | --- | -- | --- | |||||||||||
26 | BUF | --- | --- | -- | --- | --- | -- | --- | |||||||||||
27 | PIT | --- | --- | -- | --- | --- | -- | --- | |||||||||||
28 | TBD | BAL | 0.688 | * | BAL | 0.688 | --- | 4 | --- | ||||||||||
29 | TBD | CLE | 0.688 | * | CLE | 0.688 | --- | 5 | --- | ||||||||||
30 | TBD | IND | 0.688 | * | IND | 0.688 | --- | 6 | --- | ||||||||||
31 | TBD | TEN | 0.688 | * | TEN | 0.688 | --- | 7 | --- | ||||||||||
32 | MIA | --- | --- | -- | --- | --- | -- | --- | |||||||||||
33 | LV | --- | --- | -- | --- | --- | -- | --- | |||||||||||
34 | TBD | NE | 0.438 | * | NE | 0.438 | --- | 10 | --- | ||||||||||
35 | TBD | LAC | 0.438 | * | LAC | 0.438 | --- | 11 | --- | ||||||||||
36 | DEN | --- | --- | -- | --- | --- | -- | --- | |||||||||||
37 | CIN | --- | --- | -- | --- | --- | -- | --- | |||||||||||
38 | HOU | --- | --- | -- | --- | --- | -- | --- | |||||||||||
39 | NYJ | --- | --- | -- | --- | --- | -- | --- | |||||||||||
40 | JAX | --- | --- | -- | --- | --- | -- | --- | |||||||||||
Calc_Ties (Conf) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I7:I22 | I7 | =IFERROR(INDEX(Tab_TeamStat[Abr],MATCH(1,(H7=Tab_TeamStat[ConfRnk])*($H$5=Tab_TeamStat[Conf]),0)),"---") |
K7:K22 | K7 | =IFERROR(INDEX(Tab_TeamStat[Pct],MATCH($I7,Tab_TeamStat[Abr],0)),"---") |
M7:M22 | M7 | =IFERROR(INDEX(Tab_TeamStat[DivPct],MATCH($I7,Tab_TeamStat[Abr],0)),"---") |
O7:O22 | O7 | =IFERROR(INDEX(Tab_TeamStat[ConfPct],MATCH($I7,Tab_TeamStat[Abr],0)),"---") |
Q25:Q40 | Q25 | =IF(M25<>"---","*","--") |
R25:R40 | R25 | =IF(Q25<>"--",M25,"---") |
V25:V40 | V25 | =IF(Q25<>"--",IFERROR(INDEX(R$25:R$40,MATCH(W25,S$25:S$40,0)),"---"),"---") |
W25:W40 | W25 | =IF(Q25<>"--",ROWS(W$25:W25)*COLUMNS(W$25:W25),"--") |
X25:X40 | X25 | =IF(Q25<>"--",IFERROR(INDEX(T$25:T$40,MATCH(W25,S$25:S$40,0)),"---"),"---") |
T28:T31,T34:T35 | T28 | =O28 |
K25:K40 | K25 | =IF(AND(K7=K8,OR(ROWS(K$25:K25)*COLUMNS(K$25:K25)=1,ROWS(K$25:K25)*COLUMNS(K$25:K25)=16)),"TBD", IF(OR(K6=K7,K7=K8),"TBD",I7)) |
M25:M40 | M25 | =IF(K25<>"TBD","---",I7) |
O25:O40 | O25 | =IF(M25<>"---",INDEX($K$7:$K$22,MATCH(M25,$I$7:$I$22,0)),"---") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Question #2: The first tie-breaking rule is based on best head to head play percentage. I've attached the range which I have set up the rule check range. Again, how would I set up IFERROR(SUM(((0.5*(COUNTIF(xx:xx,"T"))+(COUNTIF(xx:xx,"W"))/SUM((COUNTIF(xx:xx,"W")+COUNTIF(xx:xx,"L")+(COUNTIF(xx:xx,"T"))))))),"---")), formula to calculate the percentage for various teams which have equal percentages in column CF81:CF91, (e.g., T81:T91 Z81:Z91 equal then AF81:AF91 AP81:AP91 equal),
NFL 2021-2022 Standings (Template).xlsx | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | |||
78 | Head to Head Games (Rnd 1) | ||||||||||||||||||||||||||||
79 | KC | BUF | --- | BAL | CLE | IND | |||||||||||||||||||||||
80 | |||||||||||||||||||||||||||||
81 | KC | --- | --- | W | --- | --- | --- | W | --- | --- | --- | --- | --- | ||||||||||||||||
82 | BUF | --- | L | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ||||||||||||||||
83 | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ||||||||||||||||
84 | BAL | --- | L | --- | --- | --- | --- | --- | --- | W | W | W | --- | ||||||||||||||||
85 | CLE | --- | --- | --- | --- | --- | --- | L | L | --- | --- | --- | W | ||||||||||||||||
86 | IND | --- | --- | --- | --- | --- | --- | --- | L | L | --- | --- | --- | ||||||||||||||||
87 | TEN | --- | --- | --- | W | --- | --- | W | --- | --- | L | W | L | ||||||||||||||||
88 | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ||||||||||||||||
89 | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ||||||||||||||||
90 | NE | L | --- | L | L | --- | --- | --- | W | --- | --- | --- | --- | ||||||||||||||||
91 | LAC | W | L | L | --- | --- | --- | --- | --- | --- | --- | --- | --- | ||||||||||||||||
Calc_Ties (Conf) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T79 | T79 | =$R81 |
X79 | X79 | =$R82 |
AB79 | AB79 | =$R83 |
AF79 | AF79 | =$R84 |
AJ79 | AJ79 | =$R85 |
AN79 | AN79 | =$R86 |
R83:R91 | R83 | =IF(Q27<>"--",R27,"---") |
T81:T91,AN81:AN91,AJ81:AJ91,AF81:AF91,AB81:AB91,X81:X91 | T81 | =IF(OR($R81="---",T$79="---",$R81=T$79),"---", IFERROR(INDEX(Tab_Schedule[ResultA],MATCH(1,($R81=Tab_Schedule[AbrA])*(T$79=Tab_Schedule[AbrH]),0)),"---")) |
V81:V91,AP81:AP91,AL81:AL91,AH81:AH91,AD81:AD91,Z81:Z91 | V81 | =IF(OR($R81="---",T$79="---",$R81=T$79),"---", IFERROR(INDEX(Tab_Schedule[ResultH],MATCH(1,($R81=Tab_Schedule[AbrH])*(T$79=Tab_Schedule[AbrA]),0)),"---")) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
NFL 2021-2022 Standings (Template).xlsx | |||||
---|---|---|---|---|---|
CF | CG | CH | |||
79 | Head to Head | ||||
80 | |||||
81 | |||||
82 | |||||
83 | |||||
84 | |||||
85 | |||||
86 | |||||
87 | |||||
88 | |||||
89 | |||||
90 | |||||
Calc_Ties (Conf) |