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

=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" <arron.laing.29bwyo_1150182603.731@excelforum-nospam.com>
wrote in message
news:arron.laing.29bwyo_1150182603.731@excelforum-nospam.com...
>
> 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
>

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

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.29bwyo_1150182603.731@excelforum-nospam.com>,
arron laing <arron.laing.29bwyo_1150182603.731@excelforum-nospam.com>
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

Thanks Domenic

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

Cheers

Arron