RANK Solution needed please :)

[Bennets04]

Hello All,

Im sure you will be able able to help with the following question:

I am trying to RANK a list of data but where the number maybe the same how can i get the formula to not have duplicates... So far example below i want James to be ranked 4th and Sally 5th even though they both had the same score

Name	Score	Rank
Scott	100	1
James	59	4
Sally	59	4
Beth	99	2
David	60	3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

I would really appreciate your support and advice on this

=RANK.EQ(C3,$C$3:$C$7,0)

Many thanks
Steve

 

[barry houdini]

Hello Steve, you can add a COUNTIF fuunction to get the answer you want, try this formula in D3 copied down

=RANK.EQ(C3,C$3:C$7)+COUNTIF(C$3:C3,C3)-1

 

[rpaulson]

http://www.contextures.com/excel-functions-rank.html

 

[Matt Rogers]

Wrong formula ...

 

[Bennets04]

Hello Steve, you can add a COUNTIF fuunction to get the answer you want, try this formula in D3 copied down

=RANK.EQ(C3,C$3:C$7)+COUNTIF(C$3:C3,C3)-1

This didnt work as i was hoping to pull the formula down to around 200 ranked associates

Im after a formula that will count the ranks all the way down then know if the same rank was already highlighted above it will add a number then so on.

Hope that helps

 

[Bennets04]

Just to let you i have managed to fix this

Found it on Excelisfun youtube channel

Many thanks for your thoughts all

Thanks
Steve

 

[Matty]

barry houdini's formula should work. Perhaps the wrong ranges were used. See below:

Excel 2012

	A	B	C
1	Name	Score	Rank
2	Scott	100	1
3	James	59	4
4	Sally	59	5
5	Beth	99	2
6	David	60	3

<tbody>
</tbody>

Sheet2​

Formula in C2 is:

=RANK.EQ(B2,B$2:B$6)+COUNTIF(B$2:B2,B2)-1

Which can then be copied down.

Matty

 

[barry houdini]

Im after a formula that will count the ranks all the way down then know if the same rank was already highlighted above it will add a number then so on.

That's what the formula does, Steve - if you have 200 values to rank then you just need to change the cell references appropriately, e.g. if data to rank is in C3:C202 then the formula in row 3 will be this:

=RANK.EQ(C3,C$3:C$202)+COUNTIF(C$3:C3,C3)-1

copy that down and you'll get the ranks you want

Which formula did you use?

 

[Bennets04]

Thank you for the replies... i managed it with the following

=RANK.EQ(C2,$C$2:$C$200,0)+COUNTIF($C$1:C1,C2)

Just dragged it down and worked a treat