Formula calculate rank in groups of column range

[Preacherman771]

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.

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)

 

[DRSteele]

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]

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.