RANK, duplicate ranking but no gaps in rank

[arron laing]

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

[Bob Phillips]

=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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[arron laing]

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

[Domenic]

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 <[email protected]>,
arron laing <[email protected]>
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

[arron laing]

Thanks Domenic

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

Cheers

Arron

[allana13]

Hi Folks

Sorry for hacking this thread... i have exactly the same issue.... I have tried the formula by Domenic but it gives me the rank in reverse order
I am trying to rank Percentages instead of whole numbers.

Any help please.

Thanks

[allana13]

Sorry folks please ignore

I have fixed the formula by changing the signs

Thanks

Hi Folks

Sorry for hacking this thread... i have exactly the same issue.... I have tried the formula by Domenic but it gives me the rank in reverse order
I am trying to rank Percentages instead of whole numbers.

Any help please.

Thanks

[arlu1201]

Allana,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.