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

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]Score[/TD]
[TD="class: xl63, width: 64"]Rank[/TD]
[/TR]
[TR]
[TD="class: xl63"]Scott[/TD]
[TD="class: xl63"]100[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]James[/TD]
[TD="class: xl63"]59[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Sally[/TD]
[TD="class: xl63"]59[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Beth[/TD]
[TD="class: xl63"]99[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]David[/TD]
[TD="class: xl63"]60[/TD]
[TD="class: xl63"]3
[/TD]
[/TR]
</tbody>[/TABLE]

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
Name	Score	Rank
Scott
James
Sally
Beth
David

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]3[/TD]

</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