League Table Sorting

[abee4life]

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.

 

[abee4life]

Ive got the formula for the Goal Difference onwards. Would that help for anyone?

 

[Domenic]

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!

 

[abee4life]

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

 

[Domenic]

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!