Formula calculate rank in groups of column range

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. 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.


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)),"---"))
V81:V91,AP81:AP91,AL81:AL91,AH81:AH91,AD81:AD91,Z81:Z91V81=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
CFCGCH
79Head to Head
80
81
82
83
84
85
86
87
88
89
90
Calc_Ties (Conf)
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top