Rank when there are two criteria

[ChipButtyMan]

Hi,
this is a fun problem as opposed to work related.

For American contributers info;
In football league tables
3 points for a win ie. 2-0
1 point for a draw ie. 1-1
0 points for losing

if several teams have the same amount of points, ranking is down to
goal difference.

if a team conceded no goals & scored 20 it has a goal difference of
+20
if a team conceded 5 goals & scored 20 it has a goal difference of +15
if a team conceded 20 goals & scored 5 it has a goal difference of -15


I understand how to set my worksheet up to rank for points but I don't
know how to set it up for those teams who have the same points but
rank for goal difference.
It's not rank I don't understand, it's how to rank twice I guess?
Thank you for your help & expertise everyone.

 

[Bob Phillips]

=1+COUNTIF(range_points,">"&C2)+SUMPRODUCT(--(range_GD>D2),--(range_points=C2))

where C2 is the first cell in range_Points and D2 is the first in range_GD

 

[Thomas Hardy]

Hi,
this is a fun problem as opposed to work related.

For American contributers info;
In football league tables
3 points for a win ie. 2-0
1 point for a draw ie. 1-1
0 points for losing

if several teams have the same amount of points, ranking is down to
goal difference.

if a team conceded no goals & scored 20 it has a goal difference of
+20
if a team conceded 5 goals & scored 20 it has a goal difference of +15
if a team conceded 20 goals & scored 5 it has a goal difference of -15


I understand how to set my worksheet up to rank for points but I don't
know how to set it up for those teams who have the same points but
rank for goal difference.
It's not rank I don't understand, it's how to rank twice I guess?
Thank you for your help & expertise everyone.

If you have three columns, Team, Points and GoalDiff, then just select your
data, choose Sort from the Data menu and then enter Sort by Team
(Descending) Then by GoalDiff(Descending) and your data will be sorted
according to Rank. At this point. you can enter your rank starting with 1
and filling to the bottom of your data, then you can sort in any way you
wish.

Thomas

 

[Bernd P]

Hello,

For a general approach I suggest to have a look at the more complex
example on:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd