League Table Ranking

[okopo]

I am trying to rank these teams, in the following order

1. Points
2. Goal Difference
3. Goals Scored
4. Team Name

I don't want duplicate ranks on ties.

I am using Excel 2010

[micope21]

Hi okopo

Please click attached. I done VBA to sort the table. Pos Sort Rank and Sumproduct and p. I put in =sum($D2:$F2)

Good luck

[JBeaucaire]

Create a secondary numeric value based on the items you want to include in the calculation and in the order you want them included. So, if the order you want them evaluated are:

J2
I2
H2
G2
Row position (higher in the list gets a better rank)

Then this would be the new "index score" formula...in K2 then copied down:

=J2 + (I2/100) + (H2/10000) + (G2/1000000) + (ROW()/100000000)

Then, your normal RANK() formula in A2, copied down:

=RANK($K2,$K$2:$K$21)


This should give you the correct results regardless of the ordered list, so put the list in order based on team names in the order you would break the final ties based on list order.

[Marcol]

Similar to Jerry,
Add a tie-breaker column, in this example Column J.
In J2

Please Login or Register  to view this content.

Drag/Fill Down
In L2

Please Login or Register  to view this content.

Drag Across then down.

Enter the new results in your original Table (This need not be sorted)
Changes will automatically happen in the table I have added.

[micope21]

Sorry my mistake in Rank

Do what JBeaucaire did.
K2 =J2 + (I2/100) + (H2/10000) + (G2/1000000) + (ROW()/100000000) then copy down
A2 =RANK($k2,$k$2:$k$21) then copy down will give you correct Rank. Don't put in what JBeaucaire say (=RANK($M2,$M$2:$M$21) cos nothing in row M cell it blank.)

Marcol is good. But if Tie Breaker. it come up same name. Sample first game of the season. Liverpool and Man Utd both won 2-0. That mean Tie Breaker It will show 2 Man Utd no Liverpool show up.

[Marcol]

Good point micope21

In the event of an absolute tie, commonly found early in the season, the final order is usually returned alphabetically.

The initial table should, in that case, be set up alphabetically and that should never change.

The Tie-breaker then becomes,

Please Login or Register  to view this content.

or, to vary the league size

Please Login or Register  to view this content.

See this new attachment

[micope21]

That better Marcol

I also put in RANK and POS. Rank put in =K2+J2/10^3+H2/10^6+(21-ROW(B1))/10^9 and POS put in =K2+J2/10^3+H2/10^6.

Look better in Tie breaker.

Now it up to okopo to choose or all file.

[Marcol]

@ micope21

I'm glad that you approve of my amendments, but what is the advantage that you are suggesting?

What purpose do the extra columns serve in your enhancement?

[micope21]

Marcol.

For me. I would prefer POS. You know what number they are in straight away in sec. Without it. You got to count what POS they are in.

okopo had put in POS. Maybe that what okopo want.

[Marcol]

Would this not be enough?