Sorting a Soccer PointsTable

M

Max

Hello All,
In am in need of some help please.

I am trying to sort a Soccer table.
I have used the Functions of Rank and Large to sort the ranking according to
Goal difference and Points for the teams.
The problem is, I need to display the Teams names, instead of a number that
I get from the Rank and Large functions at the end, as well as if the teams
are equal with Goal difference (GD) and Points (Pts) ranking them
alphabetically.

If you have an easier and better idea to do the sort, please let me know.
I am trying to keep the sort process as short as possible.
I have found a very lengthly solution, but using Rank and Large I am able to
get close to the ideal result, so I thought about asking the Experts for
help, to get the final result.

I have not attached the table but I will send it to who ever is willing to
help.

Thank you and best regards

Max
 
D

Domenic

Here are two different approaches. The first one uses several formulas,
whereas the second uses only one. However, the first approach is more
efficient than the second.

Assumptions:

A2:E6 contains the data

Column A contains the Team

Column D contains the Goal Difference

Column E contains the Points

[Option 1]

F2, copied down:

=D2+E2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4

G2, copied down:

=IF(N(F2),RANK(F2,$F$2:$F$6),"")

H1:

=MAX(G2:G6)

I2, copied down and across:

=IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I$2:I2),$G$2:$G$6,0)),"")

[Option 2]

I2, copied down and across:

=INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,">"
&$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6
,">"&$A$2:$A$6)/10^4,0))

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!
 
M

Max

Hello Domenic,
How do I attach an Excel spreadsheet, I will send it to you. May be this
will help.

For some or other reason I am missing it with the formulas you sent me.
I still get numbers, I need to be able to display the team names according
to the three ranking criteria.

Thank you for your help.

Max

Domenic said:
Here are two different approaches. The first one uses several formulas,
whereas the second uses only one. However, the first approach is more
efficient than the second.

Assumptions:

A2:E6 contains the data

Column A contains the Team

Column D contains the Goal Difference

Column E contains the Points

[Option 1]

F2, copied down:

=D2+E2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4

G2, copied down:

=IF(N(F2),RANK(F2,$F$2:$F$6),"")

H1:

=MAX(G2:G6)

I2, copied down and across:

=IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I$2:I2),$G$2:$G$6,0)),"")

[Option 2]

I2, copied down and across:

=INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,">"
&$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6
,">"&$A$2:$A$6)/10^4,0))

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

Max said:
Hello All,
In am in need of some help please.

I am trying to sort a Soccer table.
I have used the Functions of Rank and Large to sort the ranking according to
Goal difference and Points for the teams.
The problem is, I need to display the Teams names, instead of a number that
I get from the Rank and Large functions at the end, as well as if the teams
are equal with Goal difference (GD) and Points (Pts) ranking them
alphabetically.

If you have an easier and better idea to do the sort, please let me know.
I am trying to keep the sort process as short as possible.
I have found a very lengthly solution, but using Rank and Large I am able to
get close to the ideal result, so I thought about asking the Experts for
help, to get the final result.

I have not attached the table but I will send it to who ever is willing to
help.

Thank you and best regards

Max
Click to expand...
Click to expand...
 
D

Domenic

Did you adjust the references accordingly?

Did you confirm the formula for the second approach with
CONTROL+SHIFT+ENTER, not just ENTER?
 
M

Max

Hello Domenic,
You Champion!
Thank you, what I did was build a table to use the same cell references that
you had in your formula's and the formula's work perfectly.
Now I will adapt the formula's to my tables accordingly.

Well done and than you again.

Best regards

Max
 
M

Max

Hello Domenic,
The formulas worked well with one exception. As I don't fully understand the
formulas you used I could not get the one particular table to read correctly.

Here is the incident that the formulas don't work with. The 2nd option for
column "I" is the best as you said, but it sorts this case incorrectly for
the 1st two places only.

Column Letter ===> D E I
Group D GD Pts
Olympiacos 0 1 Roma
Valencia 3 9 Valencia
Roma 4 6 Olympiacos
Shakhtar Donetsk 0 1 Shakhtar Donetsk

According to the points Valencia should be on top then Roma and the last two
are correct.

Could you see if you would be able to sort it out.

Thnak you and best regards

Max
 
D

Domenic

That's because the solution I offered ranks first by Goal Difference and
then by Points. Based on your original post, I understood this to be
the case. Although, looking at it now, it doesn't quite make sense.
Anyway, since I can't quite make out which columns contain the data,
here are the amendments based on my original solution...

For the first approach, change the formula for F2 from...

=D2+E2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4

to

=E2+D2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4

For the second approach, the formula should be...

=INDEX(A$2:A$6,MATCH(LARGE($E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6,">"
&$A$2:$A$6)/10^4,ROWS(I$2:I2)),$E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6
,">"&$A$2:$A$6)/10^4,0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
M

Max

Hello Domenic,
I apologise for my error, I assumed the everyone understands Soccer table
ranking.
I am sorry, my mistake.

Once again very much thank you for your help.

Max
 
D

Domenic

Max said:
Hello Domenic,
I apologise for my error, I assumed the everyone understands Soccer table
ranking.
I am sorry, my mistake.
Click to expand...

No problem, no need to apologize. I should have asked for
clarification.. :)
Once again very much thank you for your help.
Click to expand...

You're very welcome! Glad I could help!

Cheers!
 
M

Michael

Can this method adapted for a third criterior " Goals for"
In soccer it is sorted by POINTS --> Goal Different ---> Goal for.

I used the formula below however when the teams have the sames point
and goal different BUT one has a greater Goals For. The teams are
tied.
I used the formula below
=D2+E2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4
then from the answer in column F

Column C is the " Goals for"
=F2+C2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4 BUT
this doubles the decimal part in column in some cases.

Help?




Here are two different approaches. The first one uses several formulas,
whereas the second uses only one. However, the first approach is more
efficient than the second.

Assumptions:

A2:E6 contains the data

Column A contains the Team

Column D contains the Goal Difference

Column E contains the Points

[Option 1]

F2, copied down:

=D2+E2/10^2+COUNTIF($A$2:$A$6,">"&A2)/10^4

G2, copied down:

=IF(N(F2),RANK(F2,$F$2:$F$6),"")

H1:

=MAX(G2:G6)

I2, copied down and across:

=IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I$2:I2),$G$2:$G$6,0)),"")

[Option 2]

I2, copied down and across:

=INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,">"
&$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6
,">"&$A$2:$A$6)/10^4,0))

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

Max said:
Hello All,
In am in need of some help please.

I am trying to sort a Soccer table.
I have used the Functions of Rank and Large to sort the ranking according to
Goal difference and Points for the teams.
The problem is, I need to display the Teams names, instead of a number that
I get from the Rank and Large functions at the end, as well as if the teams
are equal with Goal difference (GD) and Points (Pts) ranking them
alphabetically.

If you have an easier and better idea to do the sort, please let me know.
I am trying to keep the sort process as short as possible.
I have found a very lengthly solution, but using Rank and Large I am able to
get close to the ideal result, so I thought about asking the Experts for
help, to get the final result.

I have not attached the table but I will send it to who ever is willing to
help.

Thank you and best regards

Max
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help on sorting function 2
How to bulk add section breaks after a particular combination of letters/numbers 0
soccer tournament template 7
Shared Link is showing empty notebook 1
Sports summary sheet - think I put this in wrong place originally 1
Ranking data 4
Ranking - Not Sorting Properly. 16
Sorting 3

Top