RANK, duplicate ranking but no gaps in rank

  • Thread starter Thread starter arron laing
  • Start date Start date
A

arron laing

I have a list of data that I wish to rank, but I do not want the gaps i
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

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

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

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

Thanks in advance.

Arro
 
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!
 
Thanks Domenic

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

Cheers

Arro
 
I just found out about this post..

I am using the formula and it works great. However Iam trying to get it to work in reverse order.

A1: 30 R: 4
A2: 40 R: 3
A3: 40 R: 3
A4: 60 R: 2
A5: 70 R: 1

Can someone please help out?
 
Last edited:
Back
Top