# League Table Sorting

#### abee4life

##### Active Member
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;

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.
Ive got the formula for the Goal Difference onwards. Would that help for anyone?

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...

...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)

Hope this helps!

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

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!

Replies
5
Views
472
Replies
12
Views
642
Replies
5
Views
2K
Replies
3
Views
1K
Replies
1
Views
640

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.

### Which adblocker are you using?

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

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