Ranking, Names & Scores

[shah_ir85]

	A	B	C	D	E	F
1	Name	Score		Rank	Score	Name
2	Player A	1		1	5	Player E
3	Player B	2		2	4	Player D
4	Player C	3		3	3	Player C
5	Player D	4				Player H
6	Player E	5		4	2	Player B
7	Player F	1				Player G
8	Player G	2		5	1	Player A
9	Player H	3				Player F

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​
1​	Name​	Score​		Rank​	Score​	Name​
2​	Player A​	1​		1​	5​	Player E​
3​	Player B​	2​		2​	4​	Player D​
4​	Player C​	3​		3​	3​	Player C​
5​	Player D​	4​				Player H​
6​	Player E​	5​		4​	2​	Player B​
7​	Player F​	1​				Player G​
8​	Player G​	2​		5​	1​	Player A​
9​	Player H​	3​				Player F​

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.