We help IT Professionals succeed at work.

Duplicate values in RANKING affects the ranks of subsequent numbers

Andreas Hermle
on
Dear Experts:

If a column of cells in a Microsoft Excel worksheet contains duplicate values, I can use the RANK function to assign the same rank value to every occurrence of the duplicate value. The TROUBLE is that the presence of duplicate numbers affects the ranks of subsequent numbers.

For example, if the number 9 has the rank value of 2, and this number 9 occurs three times, there is no number with the rank of 3, but instead the rank value of 5 is assigned to the next number 8 (descending order).


This ranking cannot be created by Excel formulas (e.g. RANK), can it?
Points       Rank
10      1
9      2
9      2
9      2
8      3 (5 if rank formula is used)
7      4
6      5

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Comment
Watch Question

Top Expert 2011

Commented:
Assuming data starts from A1 to (at the most) a100 then try:

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

Chris
Top Expert 2011

Commented:
Sorry ignore that wrong solution!

Chris

Commented:
try this.......
Rank1 is manually put and the next rank follows.


Tils.
Book2.xls

Commented:
But that was with the assumption that your points are always in "Descending" order.
http://www.cpearson.com/excel/Rank.aspx
Put this formula:

=RANK(A1,$A$1:$A$5,0)+COUNTIF($A$1:A1,A1)-1
Most Valuable Expert 2011
Awarded 2010
Commented:
Hallo Andreas,

you can achieve this with a helper column.

Given that the values may or may not be sorted, use this for the helper column:

=LARGE($A$2:$A$8,ROW(A1))

Then create the rank with

=IF(C2=C1,D1,D1+1)

see attached for a working example.

Gruss, teylyn
Book4.xls

Commented:
Damn!! i jus replicated the "Rank" formula!!
Top Expert 2008
Commented:
Five types of rankings are discussed below. Each example assumes that the values to be ranked are numeric, in column A, and the formula is placed in an unused column. In each case the first data row is assumed to be row 2 and the formula placed in that row and then copied down to the last data row.

Duplicates Same Rank With Gaps

The formula below ranks each value in sequence from 1 to the number of values. Duplicate values have the same ranking. The smallest numbers are ranked highest. For example, the values 1, 2, 2, 3 are ranked as 1, 2, 2, 4.

   =IF(A2="","",RANK(A2,$A$2:$A$100))

The formula below ranks using the same method but ranks the highest numbers highest.

   =IF(A2="","",RANK(A2,$A$2:$A$100,TRUE))

Duplicates Same Rank Without Gaps

A common issue with the above technique is that, while duplicate values receive the same rank, there are gaps in the rank values to compensate for duplicates. To rank values without gaps use the array formula below. For example, the values 1, 2, 2, 3 are ranked as 1, 2, 2, 3.

    =IF(A2="","",SUM(1/(IF(($A$2:$A$100<A2)*($A$2:$A$100<>"")>0,COUNTIF($A$2:$A$100,$A$2:$A$100),9.999999999E+307)))+1)

The array formula below ranks using the same method but ranks the highest numbers highest.

    =IF(A2="","",SUM(1/(IF(($A$2:$A$100>A2)*($A$2:$A$100<>"")>0,COUNTIF($A$2:$A$100,$A$2:$A$100),9.999999999E+307)))+1)

Unique Ranks Without Gaps

The array formula below ranks each value from 1 to the highest number of values. Duplicate values have different rankings. The smallest numbers are ranked highest. For example, the values 1, 2, 2, 3 are ranked as 1, 2, 3, 4.

   =IF(A2="","",RANK(A2,$A$2:$A$100,0)+COUNTIF($A$2:A2,A2)-1)

The array formula below ranks using the same method as above but ranks the highest numbers highest.

   =IF(A2="","",COUNT($A$2:$A$100)-(RANK(A2,$A$2:$A$100)+COUNTIF($A$2:A2,A2)-1)+1)

Ranked Evenly Into Fixed Buckets

The array formula below ranks each value into a specific number of buckets such that the number of values in each bucket is the same or as close as possible to the same as all other buckets. The smallest numbers are ranked highest.

   =IF(A2="","",BucketCount+SmallestBucket-1-INT((SUM(IF($A$2:$A$100="","",1*(A2>$A$2:$A$100)))+1+IF(ROW(A2)-ROW($A$2)=0,0,SUM(1*(A2=OFFSET($A$2,0,0,INDEX(ROW(A2)-ROW($A$2)+1,1)-1,1)))))/(COUNTA($A$2:$A$100))*BucketCount*0.99999999999999))

The array formula below ranks using the same method as above but ranks the highest numbers highest.

   =IF(A2="","",INT((SUM(IF($A$2:$A$100="","",1*(A2>$A$2:$A$100)))+1+IF(ROW(A2)-ROW($A$2)=0,0,SUM(1*(A2=OFFSET($A$2,0,0,INDEX(ROW(A2)-ROW($A$2)+1,1)-1,1)))))/(COUNTA($A$2:$A$100))*BucketCount*0.99999999999999+SmallestBucket))

Ranked by Distribution Into Fixed Buckets

The formula below ranks each value into a specific number of buckets based on the distribution of the values. If the spectrum of values is skewed then different buckets will have different numbers of values depending on degree of skewing. Whatever the skew, the first bucket will always be SmallestBucket and the last bucket will always be SmallestBucket + BucketCount - 1. The smallest numbers are ranked highest.

   =IF(A2="","",BucketCount+SmallestBucket-1-INT((PERCENTRANK($A$2:$A$100,A2)*0.99999999999999)*BucketCount))

The formula below ranks using the same method as above but ranks the highest numbers highest.

   =IF(A2="","",INT((PERCENTRANK($A$2:$A$100,A2)*0.99999999999999)*BucketCount+SmallestBucket))

Kevin
Andreas HermleTeam leader

Author

Commented:
Dear all,

thank you very much for your swift feedback. I will put the solutions to a test and let you know.

Thank you. Regards, Andreas
Most Valuable Expert 2013
Commented:
Hello Andreas,
I see Kevin has already suggested an array formula for ranking "without gaps"
You could also use this shorter "non-array" version for ranking descending as you requested
=IF(A2="","",SUMPRODUCT((A$2:A$100>A2)/COUNTIF(A$2:A$100,A$2:A$100&""))+1)
If you want to rank with lowest value ranked 1 then that can be done like this
=IF(A2="","",SUMPRODUCT((A$2:A$100<A2)*(A$2:A$100<>"")/COUNTIF(A$2:A$100,A$2:A$100&""))+1)
regards, barry
Andreas HermleTeam leader

Author

Commented:
Hi Barry,

I randomly tested your formula first. Great, it is working. Thank you very much for your professional help. I wonder who was the first one who thought this formula out. Whoever it was, he is a genius.

I will go thru all the feedback and award points then. Anyway, your solution works just fine. Thank you very much again.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Hi teylyn,

great your workaround works fine. Thank you very much for your great help. Again, I will go thru all the feedback and award points then.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Dear all,

kind of difficult to award points. I must admit that barry submitted the best solution, since it exactly matched my requirements and the formula is fairly short.

Kevin's explanations are a superb source for future reference. Thank you very much.

Again, thank you very much for your terrific support.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
It is very difficult for me to award points equitably.

Great help. I really appreciate your help and time taken.

Regards, Andreas