Re: Sorting a Soccer PointsTable

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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!

In article <7F1EACCD-5B1C-4014-80DF-885B63E5AA9A@xxxxxxxxxxxxx>,
Max <Max@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

.

Relevant Pages

  • Re: Sorting a Soccer PointsTable
    ... 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. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Ranking a list
    ... While that may seem to work, you're not actually ranking the times. ... the same, if that's even possible, then a true "rank" will not be achieved. ... Sounds like you should sort your data first by category then by time. ... racers can be in one of 7 different categories. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Rank when there are two criteria
    ... In football league tables ... if a team conceded no goals & scored 20 it has a goal difference of ... I understand how to set my worksheet up to rank for points but I don't ... choose Sort from the Data menu and then enter Sort by Team ...
    (microsoft.public.excel)
  • Re: Ranking based on Multiple Data Fields
    ... There is a way, indeed, to get the dense rank, but again, the easiest one is to use a sequence of query. ... >> I am trying to Rank a field based on multiple sort criteria. ... >> I am currently using the following to create my Ranking: ...
    (microsoft.public.access.queries)
  • Re: Ranking numbers AND text
    ... >No, not really sorting, I don't want to have to sort them manually, but rank ... but if the formula above could be used to sort the ... The specific formula above gives the ranking of cell A1 ...
    (microsoft.public.excel.worksheet.functions)