Ranking, Names & Scores

[shah_ir85]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Name
[/TD]
[TD]Score
[/TD]
[TD][/TD]
[TD]Rank
[/TD]
[TD]Score
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Player A
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]Player E
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Player B
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]Player D
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Player C
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]Player C
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Player D
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Player H
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Player E
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]Player B
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]Player F
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Player G
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Player G
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]Player A
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]Player H
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Player F
[/TD]
[/TR]
</tbody>[/TABLE]

hye mate...i have small problem with ranking...base on score....
please help me if u understand.....the empty cell and D5,E5,D7,E7, D9 & E9....
please help me to get formula for D, E and F...tQ

 

[oldbrewer]

Re: Help with Ranking, Names & Scores

I do not at present understand what you want. Why is player D not ranked in column D ?

 

[shah_ir85]

Re: Help with Ranking, Names & Scores

Player D was at Rank No 2

 

[oldbrewer]

Re: Help with Ranking, Names & Scores

alas I do not understand what you want - what is being ranked - are these matches ie A played E

 

[jimrward]

Re: Help with Ranking, Names & Scores

Looks like A to B is players and scores, D to F is a leaderboard of sorts with the problem being duplicated ranks


If you google excel and ranking you will see a number of options using RANK and COUNTIF explore these to see if this helps as you have no other viable criteria to break ranking ties

 

[ryan0521]

Re: Help with Ranking, Names & Scores

1. Paste this to E2, =LARGE($B$2:$B$9,ROWS($E$2:E2)) then drag it down.

2. Make another column, column G, then paste this to G2, =RANK(E2,$E$2:$E$9) then drag it down.

3. CTRL + SHIFT + ENTER this to D2 =SUM(IF(G2>$G$2:$G$9,1/COUNTIF($G$2:$G$9,$G$2:$G$9)))+1 then drag it down.

4. CTRL + SHIFT + ENTER this to E2 =INDEX($A$2:$A$9, SMALL(IF(E2=$B$2:$B$9, ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1, ""), COUNTIF(E2:$E$9,E2))) then drag it down.

 

[oldbrewer]

Re: Help with Ranking, Names & Scores

why not just sort by score descending ?

 

[shah_ir85]

Re: Help with Ranking, Names & Scores

1. Paste this to E2, =LARGE($B$2:$B$9,ROWS($E$2:E2)) then drag it down.

2. Make another column, column G, then paste this to G2, =RANK(E2,$E$2:$E$9) then drag it down.

3. CTRL + SHIFT + ENTER this to D2 =SUM(IF(G2>$G$2:$G$9,1/COUNTIF($G$2:$G$9,$G$2:$G$9)))+1 then drag it down.

4. CTRL + SHIFT + ENTER this to E2 =INDEX($A$2:$A$9, SMALL(IF(E2=$B$2:$B$9, ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1, ""), COUNTIF(E2:$E$9,E2))) then drag it down.

wow...its amazing....but for the rank...how can i hide the second duplicate rank....which mean...if there are 2 rank 3...how can i hide the second number 3

 

[ryan0521]

Re: Help with Ranking, Names & Scores

What do you mean by hiding the second duplicate rank? hide the entire row? please be specific.

 

[Marcelo Branco]

I think i understand what you are looking for

See if this does what you need


[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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Score​

[/td][td][/td][td]

Rank​

[/td][td]

Score​

[/td][td]

Name​

[/td][/tr]

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

2​

[/td][td]

Player A​

[/td][td]

1​

[/td][td][/td][td]

1​

[/td][td]

5​

[/td][td]

Player E​

[/td][/tr]

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

3​

[/td][td]

Player B​

[/td][td]

2​

[/td][td][/td][td]

2​

[/td][td]

4​

[/td][td]

Player D​

[/td][/tr]

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

4​

[/td][td]

Player C​

[/td][td]

3​

[/td][td][/td][td]

3​

[/td][td]

3​

[/td][td]

Player C​

[/td][/tr]

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

5​

[/td][td]

Player D​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td]

Player H​

[/td][/tr]

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

6​

[/td][td]

Player E​

[/td][td]

5​

[/td][td][/td][td]

4​

[/td][td]

2​

[/td][td]

Player B​

[/td][/tr]

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

7​

[/td][td]

Player F​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td]

Player G​

[/td][/tr]

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

8​

[/td][td]

Player G​

[/td][td]

2​

[/td][td][/td][td]

5​

[/td][td]

1​

[/td][td]

Player A​

[/td][/tr]

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

9​

[/td][td]

Player H​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td]

Player F​

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

Array formula in D2 copied down
=IF(ISNUMBER(MATCH(SUM(IF(B$2:B$9>LARGE(B$2:B$9,ROWS(D$2:D2)),1/COUNTIF(B$2:B$9,B$2:B$9)))+1,D$1:D1,0)),"",SUM(IF(B$2:B$9>LARGE(B$2:B$9,ROWS(D$2:D2)),1/COUNTIF(B$2:B$9,B$2:B$9)))+1)
Ctrl+Shift+Enter

Regular formula in E2 copied down
=IF(D2="","",MAX(D$2:D$9)-D2+1)
Enter

Array formula in F2 copied down
=IF(D2<>"",INDEX(A$2:A$9,MATCH(E2,B$2:B$9,0)),INDEX(A$2:A$9,SMALL(IF(B$2:B$9=MIN(E$2:E2),IF(ISNA(MATCH(A$2:A$9,F$1:F1,0)),ROW(A$2:A$9)-ROW(A$2)+1)),1)))
Ctrl+Shift+Enter

Hope this helps

M.