RANK Solution needed please :)

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
58
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

NameScoreRank
Scott1001
James594
Sally594
Beth992
David603

<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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
58

ADVERTISEMENT

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
 
Last edited:

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
58
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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

Excel 2012
ABC
1NameScoreRank
2Scott1001
3James594
4Sally595
5Beth992
6David603

<tbody>
</tbody>
Sheet2
Formula in C2 is:

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

Which can then be copied down.

Matty
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jul 30, 2010
Messages
58
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,770
Members
412,617
Latest member
mlharris
Top