How to calculate rank (subrank?) in NFL conference and division?

[JenniferMurphy]

I have a sheet with the rankings of the 32 NFL teams. The rankings are based on the average of the rankings from several sports ranking websites. The overall ranking works. But I would like to be able to calculate the rank of each team withih its conference of 16 teams and its division of 4 teams. I have tested several formulas using various combinations of Rank, Filter, Index, but I either get a Spill error or Excel tells me that what I entered is not a formula.

In the minisheet below, the formulas in B8 & C8 work, but what I think is a comparable expression inside the table (F8) gets a Spill error. I entered the values in the Rank in Division column (G) manually to show what the result should be when the table is sorted by Division within Conference within Rank.

Can someone tell me what I am doing wrong and how to fix it?

I have macros that will sort the table.

'==================================================================
'     Sort #1 - Rank
'==================================================================
Sub SortByRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Rank Overall]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub


'==================================================================
'     Sort #2 - Conference, Rank
'==================================================================
Sub SortByConferenceRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Conference]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Rank Overall]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub


'==================================================================
'     Sort #3 - Conferene, Division, Rank
'==================================================================
Sub SortByConferenceDivisionRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Conference]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Division]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Rank Overall]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub

2024 NFL Power Rankings.xlsm
	A	B	C	D	E	F	G	H	I	J	K
6	R/C	B	C	D	E	F	G	H	I	J	K
7	7				Rank Overall	Rank in Conference	Rank in Division	Team	Conference	Division	Wk 14 Average Rank
8	8	TRUE	TRUE		13	#SPILL!	1	Seattle Seahawks	NFC	West	13.8
9	9	TRUE	TRUE		15	#SPILL!	2	Arizona Cardinals	NFC	West	14.2
10	10	TRUE	TRUE		16	#SPILL!	3	San Francisco 49ers	NFC	West	16.4
11	11	TRUE	TRUE		17	#SPILL!	4	Los Angeles Rams	NFC	West	16.6
12	12	TRUE	TRUE		14	#SPILL!	1	Tampa Bay Buccaneers	NFC	South	14.1
13	13	TRUE	TRUE		20	#SPILL!	2	Altanta Falcons	NFC	South	19.3
14	14	TRUE	TRUE		24	#SPILL!	3	New Orleans Saints	NFC	South	23.9
15	15	TRUE	TRUE		27	#SPILL!	4	Carolina Panthers	NFC	South	26.8
16	16	TRUE	TRUE		1	#SPILL!	1	Detroit Lions	NFC	North	1.1
17	17	TRUE	TRUE		5	#SPILL!	2	Green Bay Packers	NFC	North	5.6
18	18	TRUE	TRUE		6	#SPILL!	3	Minnesota Vikings	NFC	North	5.9
19	19	TRUE	TRUE		22	#SPILL!	4	Chicago Bears	NFC	North	22.3
Power rankings

Cell Formulas
Range	Formula
B6:K6	B6	=col()
B8:B39	B8	=TblNFL[Conference]=I8
C8:C39	C8	=TblNFL[Conference]=TblNFL[@Conference]
E8:E19	E8	=RANK.EQ([@[Wk 14 Average Rank]],[Wk 14 Average Rank],1)
F8:F19	F8	=[Conference]=[@Conference]
A7:A19	A7	=ROW()
K8:K19	K8	=AVERAGE([@[Wk 14 NFL]]:OFFSET([@[Wk 14 Record]],0,-1))
Dynamic array formulas.

 

[StephenCrump]

Perhaps?

=1+COUNTIFS([Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]],[Conference],[@Conference],[Division],[@Division])

 

[JenniferMurphy]

Perhaps?

=1+COUNTIFS([Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]],[Conference],[@Conference],[Division],[@Division])

If I put that in F8 or G8, it displays the expression as text. I checked the formatting.It was General, I changed it to Number (0).

2024 NFL Power Rankings.xlsm
	E	F	G	H	I	J	K
6	E	F	G	H	I	J	K
7	Rank Overall	Rank in Conference	Rank in Division	Team	Conference	Division	Wk 14 Average Rank
8	13	=1+COUNTIFS([Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]],[Conference],[@Conference],[Division],[@Division])	=1+COUNTIFS([Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]],[Conference],[@Conference],[Division],[@Division])	Seattle Seahawks	NFC	West	13.8
9	15		2	Arizona Cardinals	NFC	West	14.2
10	16		3	San Francisco 49ers	NFC	West	16.4
11	17		4	Los Angeles Rams	NFC	West	16.6
12	14		1	Tampa Bay Buccaneers	NFC	South	14.1
13	20		2	Altanta Falcons	NFC	South	19.3
14	24		3	New Orleans Saints	NFC	South	23.9
15	27		4	Carolina Panthers	NFC	South	26.8
Power rankings

Cell Formulas
Range	Formula
E6:K6	E6	=col()
E8:E15	E8	=RANK.EQ([@[Wk 14 Average Rank]],[Wk 14 Average Rank],1)
K8:K15	K8	=AVERAGE([@[Wk 14 NFL]]:OFFSET([@[Wk 14 Record]],0,-1))

 

[StephenCrump]

Select cell F8, hit the F2 key to edit, and press the ENTER key.

Then it should show as numeric?

 

[JenniferMurphy]

I don't know what was wrong before. I reentered your expression, after I studied it a little, and it works. Here's the entire minisheet.

Thank you

2024 NFL Power Rankings.xlsm
	B	C	D	E	F	G	H
6	Rank Overall	Rank in Conference	Rank in Division	Team	Conference	Division	Wk 14 Average Rank
7	1	1	1	Detroit Lions	NFC	North	1.1
8	3	2	1	Philadelphia Eagles	NFC	East	3.7
9	5	3	2	Green Bay Packers	NFC	North	5.6
10	6	4	3	Minnesota Vikings	NFC	North	5.9
11	10	5	2	Washington Commanders	NFC	East	11.6
12	13	6	1	Seattle Seahawks	NFC	West	13.8
13	14	7	1	Tampa Bay Buccaneers	NFC	South	14.1
14	15	8	2	Arizona Cardinals	NFC	West	14.2
15	16	9	3	San Francisco 49ers	NFC	West	16.4
16	17	10	4	Los Angeles Rams	NFC	West	16.6
17	20	11	2	Altanta Falcons	NFC	South	19.3
18	22	12	4	Chicago Bears	NFC	North	22.3
19	23	13	3	Dallas Cowboys	NFC	East	23.8
20	24	14	3	New Orleans Saints	NFC	South	23.9
21	27	15	4	Carolina Panthers	NFC	South	26.8
22	32	16	4	New York Giants	NFC	East	31.3
23	2	1	1	Buffalo Bills	AFC	East	2.0
24	4	2	1	Kansas City Chiefs	AFC	West	4.2
25	7	3	1	Baltimore Ravens	AFC	North	6.8
26	8	4	2	Pittsburgh Steelers	AFC	North	7.7
27	9	5	2	Los Angeles Chargers	AFC	West	9.6
28	10	6	1	Houston Texans	AFC	South	11.6
29	12	7	3	Denver Broncos	AFC	West	12.8
30	18	8	2	Miami Dolphins	AFC	East	17.9
31	19	9	3	Cincinnati Bengals	AFC	North	19.1
32	21	10	2	Indianapolis Colts	AFC	South	19.5
33	25	11	4	Cleveland Browns	AFC	North	25.3
34	26	12	3	New York Jets	AFC	East	25.9
35	28	13	3	Tennessee Titans	AFC	South	27.6
36	29	14	4	New England Patriots	AFC	East	28.3
37	30	15	4	Las Vegas Raiders	AFC	West	29.6
38	31	16	4	Jacksonville Jaguars	AFC	South	30.1
Power rankings

Cell Formulas
Range	Formula
B7:B38	B7	=RANK.EQ([@[Wk 14 Average Rank]],[Wk 14 Average Rank],1)
C7:C38	C7	=COUNTIFS([Conference],[@Conference],[Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]])+1
D7:D38	D7	=COUNTIFS([Conference],[@Conference],[Division],[@Division],[Wk 14 Average Rank],"<"&[@[Wk 14 Average Rank]])+1
H7:H38	H7	=AVERAGE([@[Wk 14 NFL]]:OFFSET([@[Wk 14 Record]],0,-1))

 

[StephenCrump]

No problem. I'm glad you got it working.

 

[JenniferMurphy]

No problem. I'm glad you got it working.

I wasn't saying that your expression was wrong. I probably entered it wrong. The one I have working is essentially the same as yours. You solved it.

Thanks