RANK, duplicate ranking but no gaps in rank
 

I have a list of data that I wish to rank, but I do not want the gaps in
the ranking numbers created by duplicates in the data, ie.

Data Rank Req
1 1
2 2
3 3
3 3
6 4
7 5

Thanks

Arron


--
arron laing
------------------------------------------------------------------------
arron laing's Profile: http://www.excelforum.com/member.php...o&userid=11761
View this thread: http://www.excelforum.com/showthread...hreadid=551307

RANK, duplicate ranking but no gaps in rank
 

=RANK(A1,$A$1:$A$10,1)+COUNTIF($A$1:$A1,A1)-1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"arron laing"
wrote in message
...

I have a list of data that I wish to rank, but I do not want the gaps in
the ranking numbers created by duplicates in the data, ie.

Data Rank Req
1 1
2 2
3 3
3 3
6 4
7 5

Thanks

Arron


--
arron laing
------------------------------------------------------------------------
arron laing's Profile:
http://www.excelforum.com/member.php...o&userid=11761
View this thread: http://www.excelforum.com/showthread...hreadid=551307

RANK, duplicate ranking but no gaps in rank
 

Thanks for replying Bob but that is not not I am after.

I believe your solution gives unique rankings, whereas I am after a
solution that allows duplicate rankings but where the rank numbers are
continuous, ie. no gaps.

Note that the 3s in the data col get a rank of 3, and the next data
entry, 6, gets a rank of 4, not 5 as the standard RANK() return would
be.

Thanks in advance.

Arron


--
arron laing
------------------------------------------------------------------------
arron laing's Profile: http://www.excelforum.com/member.php...o&userid=11761
View this thread: http://www.excelforum.com/showthread...hreadid=551307

RANK, duplicate ranking but no gaps in rank
 

Assuming that A2:A7 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

B2, copied down:

=SUM(IF(A2$A$2:$A$7,1/COUNTIF($A$2:$A$7,$A$2:$A$7)))+1

Hope this helps!

In article ,
arron laing
wrote:

I have a list of data that I wish to rank, but I do not want the gaps in
the ranking numbers created by duplicates in the data, ie.

Data Rank Req
1 1
2 2
3 3
3 3
6 4
7 5

Thanks

Arron

RANK, duplicate ranking but no gaps in rank
 

Thanks Domenic

That is exactly what I am after - and not a RANK() to be seen!

Cheers

Arron


--
arron laing
------------------------------------------------------------------------
arron laing's Profile: http://www.excelforum.com/member.php...o&userid=11761
View this thread: http://www.excelforum.com/showthread...hreadid=551307