Ranking based on 3 criteria (sports table)

[ReservoirDodds]

Hello,

Firstly, I have often browsed this forum for solutions and found it superbly useful, so thank you all!

Now, to my question. I have created myself a type of predictor for football (soccer) division tables, where I update each matchday with the up to date table and a secondary table updates based upon a ranking worked out from points per game (PPG), then a predicted goal difference (worked out much the same as the PPG). The issue is, occasionally there are times when a team has the same PPG and also the same predicted goal difference and as such I get error messages. With that in mind, I am hoping I can rectify this issue by including a third ranking criteria, this time being goals scored.

It should be weighted in the following order:
1st ranking should be PPG, the higher the number the higher the rank.
2nd ranking should then be goal difference, again, the higher the number, the higher the rank.
3rd ranking should then be goals for, once more, the higher the number, the higher the rank.

These rankings would only ideally come into effect when there is a tie in ranking, so for instance I have two teams on 55 points, the formula would then rank goal difference to determine which is 1st and which is second. If this is also equal, it then looks at the third criteria.

I have posted a snippet from one of the leagues below. Ordinarily most of the columns are hidden, hence it looking messy. Please do let me know if there are areas I can tidy up too. Essentially, what I is to be able to update the left hand table with the latest divisional table, and have the right hand table update automatically to reflect a prediction of the end of season table. Thanks!

MrExcel Forum Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Final Position	Club	Games Played	Points	PPG	Final Points	Hidden Points	Expected Position	GF	GA	GD								Pts	GD
2	1	1	Man City	26	62	2.385	91	90.615385	1	52	16	36	1.384615385	52.61538		Total Games		1	Man City	91	53
3	2	2	Man Utd	26	50	1.923	73	73.076923	2	53	32	21	0.807692308	30.69231		38		2	Man Utd	73	31
4	3	3	Leicester	26	49	1.885	72	71.615385	3	45	30	15	0.576923077	21.92308				3	Leicester	72	22
5	4	4	West Ham	26	45	1.731	66	65.769231	4	40	31	9	0.346153846	13.15385				4	West Ham	66	13
6	5	5	Chelsea	26	44	1.692	64	64.307692	5	41	25	16	0.615384615	23.38462				5	Chelsea	64	23
7	6	7	Liverpool	26	43	1.654	63	62.846154	7	47	34	13	0.5	19				6	Everton	63	8
8	7	6	Everton	24	40	1.667	63	63.333333	6	38	33	5	0.208333333	7.916667				7	Liverpool	63	19
9	8	9	Tottenham	25	39	1.56	59	59.28	9	41	27	14	0.56	21.28				8	Aston Villa	62	19
10	9	8	Aston Villa	24	39	1.625	62	61.75	8	38	26	12	0.5	19				9	Tottenham	59	21
11	10	10	Arsenal	26	37	1.423	54	54.076923	10	34	27	7	0.269230769	10.23077				10	Arsenal	54	10
12	11	11	Leeds	26	35	1.346	51	51.153846	11	43	44	-1	-0.03846154	-1.46154				11	Leeds	51	-1
13	12	12	Wolves	26	34	1.308	50	49.692308	12	27	33	-6	-0.23076923	-8.76923				12	Wolves	50	-9
14	13	13	Crystal Palace	26	33	1.269	48	48.230769	13	29	43	-14	-0.53846154	-20.4615				13	Crystal Palace	48	-20
15	14	14	Southampton	25	30	1.2	46	45.6	14	31	44	-13	-0.52	-19.76				14	Southampton	46	-20
16	15	15	Burnley	26	28	1.077	41	40.923077	15	18	34	-16	-0.61538462	-23.3846				15	Burnley	41	-23
17	16	16	Brighton	26	26	1	38	38	16	26	33	-7	-0.26923077	-10.2308				16	Brighton	38	-10
18	17	17	Newcastle	26	26	1	38	38	16	27	44	-17	-0.65384615	-24.8462				17	Newcastle	38	-25
19	18	18	Fulham	26	23	0.885	34	33.615385	18	21	32	-11	-0.42307692	-16.0769				18	Fulham	34	-16
20	19	19	West Brom	26	17	0.654	25	24.846154	19	20	55	-35	-1.34615385	-51.1538				19	West Brom	25	-51
21	20	20	Sheff Utd	26	11	0.423	16	16.076923	20	15	43	-28	-1.07692308	-40.9231				20	Sheff Utd	16	-41
Premier

Cell Formulas
Range	Formula
F2:F21	F2	=SUM(E2/D2)
G2:G21	G2	=H2
H2:H21	H2	=SUM($P$3*F2)
I2:I21	I2	=RANK(H2,$H$2:$H$25,0)
L2:L21	L2	=SUM(J2-K2)
M2:M21	M2	=L2/D2
N2:N21	N2	=M2*$P$3
S2	S2	=VLOOKUP(1,$B$2:$C$25,2,FALSE)
T2:T21	T2	=VLOOKUP(S2,$C$2:$H$25,6,FALSE)
U2:U21	U2	=VLOOKUP(S2,$C$2:$N$21,12,FALSE)
S3	S3	=VLOOKUP(2,$B$2:$C$25,2,FALSE)
S4	S4	=VLOOKUP(3,$B$2:$C$25,2,FALSE)
S5	S5	=VLOOKUP(4,$B$2:$C$25,2,FALSE)
S6	S6	=VLOOKUP(5,$B$2:$C$25,2,FALSE)
S7	S7	=VLOOKUP(6,$B$2:$C$25,2,FALSE)
S8	S8	=VLOOKUP(7,$B$2:$C$25,2,FALSE)
S9	S9	=VLOOKUP(8,$B$2:$C$25,2,FALSE)
S10	S10	=VLOOKUP(9,$B$2:$C$25,2,FALSE)
S11	S11	=VLOOKUP(10,$B$2:$C$25,2,FALSE)
S12	S12	=VLOOKUP(11,$B$2:$C$25,2,FALSE)
S13	S13	=VLOOKUP(12,$B$2:$C$25,2,FALSE)
S14	S14	=VLOOKUP(13,$B$2:$C$25,2,FALSE)
S15	S15	=VLOOKUP(14,$B$2:$C$25,2,FALSE)
S16	S16	=VLOOKUP(15,$B$2:$C$25,2,FALSE)
S17	S17	=VLOOKUP(16,$B$2:$C$25,2,FALSE)
S18	S18	=VLOOKUP(17,$B$2:$C$25,2,FALSE)
S19	S19	=VLOOKUP(18,$B$2:$C$25,2,FALSE)
S20	S20	=VLOOKUP(19,$B$2:$C$25,2,FALSE)
S21	S21	=VLOOKUP(20,$B$2:$C$25,2,FALSE)
B2:B21	B2	=RANK.EQ($G2,$G$2:$G$21)+COUNTIFS($G$2:$G$21,$G2,$N$2:$N$21,">"&$N2)

 

[DRSteele]

Welcome to the forum.

Review this article about ranking with cascading levels of tie-breakers.