RANK Solution needed please :)

Bennets04

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

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
54
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
54
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,707
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
54
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 :)
 

Forum statistics

Threads
1,081,535
Messages
5,359,365
Members
400,525
Latest member
swwber

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top