League Table Sorting

abee4life

Active Member
Joined
Jan 27, 2005
Messages
319
Can anyone please tell me how i can organise a league table to be put into this order from top to bottom?

My data are in cells:

Team A1
Played B1
Won C1
Drawn D1
Lost E1
Scored F1
Conceded G1
Goal Difference H1
Points I1

As its for the World Cup, there are 4 nations in a group.

The order they go in are:

(a) greater number of points obtained in all the group matches;
If two, or more, teams are equal on the basis of the above criteria, their ranking shall be determined as follows:
(b) greater number of points obtained in the group matches between the teams concerned;
(c) goal difference resulting from the group matches between the teams concerned;
(d) greater number of goals scored in the group matches between the teams concerned;
(e) goal difference in all group matches;
(f) greater number of goals scored in all group matches;

Can anyone please help?

if you need for me to mention where the games are recorded, i can let you know.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I'm not sure I understand fully, but see if this is what you're looking for...

Since part of the ranking is based on the group in which the team belongs, I assume you must have a column indicating the group. Let's say that Column J contains the 'group name'. Furthermore, assuming...

A1:J1 contains your headers/labels

A2:J50 contains your data

...try the following, which involves several helper columns in order to arrive at the final ranking...

L2, copied down:

=(SUMPRODUCT(--($J$2:$J$50=J2),--(I2>$I$2:$I$50))+1)*10^5

M2, copied down:

=(SUMPRODUCT(--($J$2:$J$50=J2),--(H2>$H$2:$H$50))+1)*10^3

N2, copied down:

=(SUMPRODUCT(--($J$2:$J$50=J2),--(F2>$F$2:$F$50))+1)*10

O2, copied down:

=I2*10^10+L2+M2+N2+H2/10^2+F2/10^4

P2, copied down:

=RANK(O2,$O$2:$O$50)

Adjust the ranges accordingly.

Hope this helps!
 
Upvote 0
I wrote all that down and copied down, but it didnt work out as planned.

I wrote in some scorelines, and 2 teams had 6 points, 2 had 3, but both sets finished 1 and 3.. so were not split.

In these scenarios, they would be split on results between the two teams.

So team A and Team D finished on 3 points and between them, Team A won 1-0, So team A would finish higher, but if Team D had won, Team D would finish higher. In the event of them drawing, its down to goal difference, goals scored, then alphabetical order
 
Upvote 0
I completely misunderstood your ranking order. In addition, your description of the ranking order differs, somewhat, from your original one. If I understand you correctly, you'd like the ranking on the following basis...

* points
* games won
* goal difference
* goals scored
* alphabetical order

If this is correct, try the following...

L2, copied down:

=I2*10^6+C2*10^4+H2*10^2+F2/10^4+COUNTIF($A$2:$A$50,">"&A2)/10^6

M2, copied down:

=RANK(L2,$L$2:$L$50)

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,216,099
Messages
6,128,822
Members
449,470
Latest member
Subhash Chand

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