# Formula calculate rank in groups of column range

#### Preacherman771

##### New Member
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.

Cell Formulas
RangeFormula
I7:I22I7=IFERROR(INDEX(Tab_TeamStat[Abr],MATCH(1,(H7=Tab_TeamStat[ConfRnk])*(\$H\$5=Tab_TeamStat[Conf]),0)),"---")
K7:K22K7=IFERROR(INDEX(Tab_TeamStat[Pct],MATCH(\$I7,Tab_TeamStat[Abr],0)),"---")
M7:M22M7=IFERROR(INDEX(Tab_TeamStat[DivPct],MATCH(\$I7,Tab_TeamStat[Abr],0)),"---")
O7:O22O7=IFERROR(INDEX(Tab_TeamStat[ConfPct],MATCH(\$I7,Tab_TeamStat[Abr],0)),"---")
Q25:Q40Q25=IF(M25<>"---","*","--")
R25:R40R25=IF(Q25<>"--",M25,"---")
V25:V40V25=IF(Q25<>"--",IFERROR(INDEX(R\$25:R\$40,MATCH(W25,S\$25:S\$40,0)),"---"),"---")
W25:W40W25=IF(Q25<>"--",ROWS(W\$25:W25)*COLUMNS(W\$25:W25),"--")
X25:X40X25=IF(Q25<>"--",IFERROR(INDEX(T\$25:T\$40,MATCH(W25,S\$25:S\$40,0)),"---"),"---")
T28:T31,T34:T35T28=O28
K25:K40K25=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:M40M25=IF(K25<>"TBD","---",I7)
O25:O40O25=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),

Cell Formulas
RangeFormula
T79T79=\$R81
X79X79=\$R82
AB79AB79=\$R83
AF79AF79=\$R84
AJ79AJ79=\$R85
AN79AN79=\$R86
R83:R91R83=IF(Q27<>"--",R27,"---")
T81:T91,AN81:AN91,AJ81:AJ91,AF81:AF91,AB81:AB91,X81:X91T81=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)),"---"))
Press CTRL+SHIFT+ENTER to enter array formulas.

NFL 2021-2022 Standings (Template).xlsx
CFCGCH
80
81
82
83
84
85
86
87
88
89
90
Calc_Ties (Conf)

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### DRSteele

##### Well-known Member
Review this MrExcel Article to see if it helps you. The nature of the problem is that you've got cascading rules for breaking ties. Function RANK.EQ is not designed for such a task.

#### Preacherman771

##### New Member
Thank you for the reply and article link. I'll check it out. One thing that I may not have made clear in my post is that I'm creating the spreadsheet as a stand alone permanent project for seasons to come. So the calculations will change as the weeks and seasons go by. As the weekly schedule results change, I am attempting to have the resulting calculations automatically computed and calculated based on formulas.

Replies
4
Views
137
Replies
1
Views
87
Replies
2
Views
244
Replies
0
Views
68
Replies
8
Views
195

1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back