Ranking based on 3 criteria (sports table)

ReservoirDodds

New Member
Joined
Mar 1, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJKLMNOPQRSTU
1Final PositionClubGames PlayedPointsPPGFinal PointsHidden PointsExpected PositionGFGAGDPtsGD
211Man City26622.3859190.61538515216361.38461538552.61538Total Games1Man City9153
322Man Utd26501.9237373.07692325332210.80769230830.69231382Man Utd7331
433Leicester26491.8857271.61538534530150.57692307721.923083Leicester7222
544West Ham26451.7316665.7692314403190.34615384613.153854West Ham6613
655Chelsea26441.6926464.30769254125160.61538461523.384625Chelsea6423
767Liverpool26431.6546362.84615474734130.5196Everton638
876Everton24401.6676363.3333336383350.2083333337.9166677Liverpool6319
989Tottenham25391.565959.2894127140.5621.288Aston Villa6219
1098Aston Villa24391.6256261.7583826120.5199Tottenham5921
111010Arsenal26371.4235454.07692310342770.26923076910.2307710Arsenal5410
121111Leeds26351.3465151.153846114344-1-0.03846154-1.4615411Leeds51-1
131212Wolves26341.3085049.692308122733-6-0.23076923-8.7692312Wolves50-9
141313Crystal Palace26331.2694848.230769132943-14-0.53846154-20.461513Crystal Palace48-20
151414Southampton25301.24645.6143144-13-0.52-19.7614Southampton46-20
161515Burnley26281.0774140.923077151834-16-0.61538462-23.384615Burnley41-23
171616Brighton262613838162633-7-0.26923077-10.230816Brighton38-10
181717Newcastle262613838162744-17-0.65384615-24.846217Newcastle38-25
191818Fulham26230.8853433.615385182132-11-0.42307692-16.076918Fulham34-16
201919West Brom26170.6542524.846154192055-35-1.34615385-51.153819West Brom25-51
212020Sheff Utd26110.4231616.076923201543-28-1.07692308-40.923120Sheff Utd16-41
Premier
Cell Formulas
RangeFormula
F2:F21F2=SUM(E2/D2)
G2:G21G2=H2
H2:H21H2=SUM($P$3*F2)
I2:I21I2=RANK(H2,$H$2:$H$25,0)
L2:L21L2=SUM(J2-K2)
M2:M21M2=L2/D2
N2:N21N2=M2*$P$3
S2S2=VLOOKUP(1,$B$2:$C$25,2,FALSE)
T2:T21T2=VLOOKUP(S2,$C$2:$H$25,6,FALSE)
U2:U21U2=VLOOKUP(S2,$C$2:$N$21,12,FALSE)
S3S3=VLOOKUP(2,$B$2:$C$25,2,FALSE)
S4S4=VLOOKUP(3,$B$2:$C$25,2,FALSE)
S5S5=VLOOKUP(4,$B$2:$C$25,2,FALSE)
S6S6=VLOOKUP(5,$B$2:$C$25,2,FALSE)
S7S7=VLOOKUP(6,$B$2:$C$25,2,FALSE)
S8S8=VLOOKUP(7,$B$2:$C$25,2,FALSE)
S9S9=VLOOKUP(8,$B$2:$C$25,2,FALSE)
S10S10=VLOOKUP(9,$B$2:$C$25,2,FALSE)
S11S11=VLOOKUP(10,$B$2:$C$25,2,FALSE)
S12S12=VLOOKUP(11,$B$2:$C$25,2,FALSE)
S13S13=VLOOKUP(12,$B$2:$C$25,2,FALSE)
S14S14=VLOOKUP(13,$B$2:$C$25,2,FALSE)
S15S15=VLOOKUP(14,$B$2:$C$25,2,FALSE)
S16S16=VLOOKUP(15,$B$2:$C$25,2,FALSE)
S17S17=VLOOKUP(16,$B$2:$C$25,2,FALSE)
S18S18=VLOOKUP(17,$B$2:$C$25,2,FALSE)
S19S19=VLOOKUP(18,$B$2:$C$25,2,FALSE)
S20S20=VLOOKUP(19,$B$2:$C$25,2,FALSE)
S21S21=VLOOKUP(20,$B$2:$C$25,2,FALSE)
B2:B21B2=RANK.EQ($G2,$G$2:$G$21)+COUNTIFS($G$2:$G$21,$G2,$N$2:$N$21,">"&$N2)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum.

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

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,798
Members
415,856
Latest member
jimb2k

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
Top