unique ranking

[Lister]

A B C D E F G H I
1 Players 1 2 3 4 Total pts Sets Won Rank
2 1 john X 2 1 2 5 9
3 2 james 1 X 1 2 4 5
4 3 mathew 2 2 X 1 5 5
5 4 ace 1 1 2 X 4 5


Hi I am sorry I don't know how to post to look like it's on excel. I want a unique ranking first base on total points, then ties will base on sets won and if there are still ties I will base them on win over the other. So in this case base on total points number 1 is john and Mathew then number 2 is james and ace. Now checking the sets won(on different data) number 1 is john and number 2 is Mathew then number 3 are james and ace. then lastly checking on the games James (2) Vs Ace (4) winner is james scoring 2 points against ace only 1 point. there number 3 is james and 4 is ace. I need a formula for this. Thanks a lot for helping.

 

[Lister]

[table="width: 500, class: Grid, align: Center"]
[tr]
[td][/td]
[td]a[/td]
[td]b[/td]
[td]c[/td]
[td]d[/td]
[td]e[/td]
[td]f[/td]
[td]g[/td]
[td]h[/td]
[td]i[/td]
[/tr]
[tr]
[td]1[/td]
[td]Bracket[/td]
[td]A[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[td]total points[/td]
[td]sets won[/td]
[td]rank[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]john[/td]
[td]x[/td]
[td]2[/td]
[td]1[/td]
[td]2[/td]
[td]5[/td]
[td]9[/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]2[/td]
[td]james[/td]
[td]1[/td]
[td]x[/td]
[td]1[/td]
[td]2[/td]
[td]4[/td]
[td]5[/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td]3[/td]
[td]mathew[/td]
[td]2[/td]
[td]2[/td]
[td]x[/td]
[td]1[/td]
[td]5[/td]
[td]5[/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]4[/td]
[td]ace[/td]
[td]1[/td]
[td]1[/td]
[td]2[/td]
[td]x[/td]
[td]4[/td]
[td]5[/td]
[td][/td]
[/tr]
[/table]

I hope this table lets you imagine what I want..thanks.

 

[Marcelo Branco]

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Bracket​

[/td][td]

Players​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

Total pts​

[/td][td]

Sets Won​

[/td][td]

Rank​

[/td][td]

TieBreaker​

[/td][td]

Final Rank​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

john​

[/td][td]

X​

[/td][td]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

5​

[/td][td]

9​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

james​

[/td][td]

1​

[/td][td]

X​

[/td][td]

1​

[/td][td]

2​

[/td][td]

4​

[/td][td]

5​

[/td][td]

3​

[/td][td]

0​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

mathew​

[/td][td]

2​

[/td][td]

2​

[/td][td]

X​

[/td][td]

1​

[/td][td]

5​

[/td][td]

5​

[/td][td]

2​

[/td][td]

0​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

ace​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

X​

[/td][td]

4​

[/td][td]

5​

[/td][td]

3​

[/td][td]

1​

[/td][td]

4​

[/td][/tr]
[/table]

Formula in I2 copied down
=COUNTIF($G$2:$G$5,">"&G2)+COUNTIFS(G$2:G$5,G2,H$2:H$5,">"&H2)+1

Array formula in J2 copied down
=IF(COUNTIF(I$2:I$5,I2)=2,IF(INDEX($C$2:$F$5,SMALL(IF(ROW(I$2:I$5)<>ROW(I2),IF(I$2:I$5=I2,A$2:A$5)),1),A2)>INDEX($C$2:$F$5,A2,SMALL(IF(ROW(I$2:I$5)<>ROW(I2),IF(I$2:I$5=I2,A$2:A$5)),1)),1,0),0)
Ctrl+Shift+Enter

Formula in K2 copied down
=I2+J2

Remark
It works with a tie between 2 players. If a tie occurs between 3 players, an unresolved case can be reached. for example:
Player 1 beats Player 2; Player 2 beats player 3; Player 3 beats Player 1.
Flip a coin?

Hope this helps

M.