Hello,
I need some help to understand a formula that was written for me using a
table that had 4 teams in it. It worked perfectly, ie in the UEFA Champions
league. Now I want to use the same formula that will allow me to sort 32
teams.
It only sorts partially with the 32 teams.
The formula is
{=INDEX(Teams,MATCH(LARGE(GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,ROWS($J$7:$J7)),GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,0))}
Teams is the range name for the 32 teams, GD is the range name for the goal
difference and Pts is the range name for the points that each team has
received after each game. (This is soccer by the way.)
It must sort points first, then if the points are equal the goal difference
then the alphabetical order of the team names.

I cannot remember the persons name that helped me with the formula. It was
here on the group discussion forum.

Thank you for your help in advance.

Max

Max,

I have to say that I don't fully understand how the function works, but it
appears to be sorting GD first, not Pts. If your results are consistent with
this, then just interchange the array names in the formula. The division is
what is giving you the sort priority -- so by diving Pts by 100 it is making
it less important than GD.



"Max" wrote:

Hello,
I need some help to understand a formula that was written for me using a
table that had 4 teams in it. It worked perfectly, ie in the UEFA Champions
league. Now I want to use the same formula that will allow me to sort 32
teams.
It only sorts partially with the 32 teams.
The formula is
{=INDEX(Teams,MATCH(LARGE(GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,ROWS($J$7:$J7)),GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,0))}
Teams is the range name for the 32 teams, GD is the range name for the goal
difference and Pts is the range name for the points that each team has
received after each game. (This is soccer by the way.)
It must sort points first, then if the points are equal the goal difference
then the alphabetical order of the team names.

I cannot remember the persons name that helped me with the formula. It was
here on the group discussion forum.

Thank you for your help in advance.

Max

Hello Art,
Well done and thank you.
The option to make the change worked.
I have gone back and checked the Group type tables and yes you are correct,
the GD is being ranked first.

Thank you and again and well done.

Much appreciated.

Max



"Art" wrote:

Max,

I have to say that I don't fully understand how the function works, but it
appears to be sorting GD first, not Pts. If your results are consistent with
this, then just interchange the array names in the formula. The division is
what is giving you the sort priority -- so by diving Pts by 100 it is making
it less important than GD.



"Max" wrote:

Hello,
I need some help to understand a formula that was written for me using a
table that had 4 teams in it. It worked perfectly, ie in the UEFA Champions
league. Now I want to use the same formula that will allow me to sort 32
teams.
It only sorts partially with the 32 teams.
The formula is
{=INDEX(Teams,MATCH(LARGE(GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,ROWS($J$7:$J7)),GD+Pts/10^2+COUNTIF(Teams,""&Teams)/10^4,0))}
Teams is the range name for the 32 teams, GD is the range name for the goal
difference and Pts is the range name for the points that each team has
received after each game. (This is soccer by the way.)
It must sort points first, then if the points are equal the goal difference
then the alphabetical order of the team names.

I cannot remember the persons name that helped me with the formula. It was
here on the group discussion forum.

Thank you for your help in advance.

Max