Solved ranking numbers in excel

September 21, 2014 at 01:30:24
Specs: Windows 7
how can i rank numbers in a column to show as 1st 2nd 3rd etc up to 150th with same values not ranked the same

See More: ranking numbers in excel

Report •


✔ Best Answer
September 21, 2014 at 10:30:06
This is a common question/issue regarding the RANK function. If the same number appears more than once, you have a tie and some Rank values are omitted.

Note what happens with the value 42 and the Rank value 4:


	 A	    B
1	50	    2
2	42	    3
3	36	    5
4	42	    3
5	52	    1

There are a number of ways to solve this issue.

Chip Pearson has a solution here:

http://www.cpearson.com/excel/Rank....

By using this formula you can eliminate any ties:

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

One possible issue with this method is that whichever duplicate value comes first in the list will always have the higher ranking. That can be "unfair" in e.g. an alphabetical list. If Barb and Sue both have a value or 42, Barb will always come first based on her name.

Another option that is a lot more random is to use 2 "Helper Columns" and the RAND function to create a unique random number to add to each value and then rank the Sum.

    A        B                 C                  D
1   50  =RAND()*.0001     =SUM(A1,B1)    =RANK(C1,$C$1:$C$5,0)
2   42	Drag Down to B5   Drag Down to C5   Drag Down to D5
3   36	   
4   42
5   52

RAND()*.0001 will create a very, very small positive number. With Excel's level of precision the odds of getting a duplicate number in 150 random values, and then having those duplicates align with the duplicates in your list is extremely small.

Now, do a Copy/PasteSpecial Values on those random number to lock them in place. If you allow them to change, then you have the possibility of the tied values changing their rank each time the sheet recalculates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
September 21, 2014 at 06:41:36
Perhaps I'm a little slow here.. it is a weekend and all that.. But I'm not at all clear as to what you actually want to do...

Can you give an example of say 5 numbers (ranked etc.); what you are staring with and so on?


Report •

#2
September 21, 2014 at 07:59:07
Also, not sure what it is your trying to do, but If you have a list of values in column A like:
     A  
 1) 60  
 2) 85  
 3) 71  
 4) 46  
 5) 33  
 6) 68  
 7) 69  
 8) 97  
 9) 49  
10) 45  

Then in cell B1 use the =RANK() function and drag down 10 rows.

=RANK(A1,$A$1:$A$10,0)

If the order is 0 or omitted, ranks number in descending order.
If the order is any nonzero value, ranks number in ascending order.

You get this result:

     A      B
 1) 60      6
 2) 85      2
 3) 71      3
 4) 46      8
 5) 33      10
 6) 68      5    
 7) 69      4
 8) 97      1
 9) 49      7
10) 45      9

Where column B lists the Rank of the value in A
within the whole group of 10 values.

Is that what you want?

MIKE

http://www.skeptic.com/


Report •

#3
September 21, 2014 at 09:56:16
OK, I reread your post, and I think this is what you want:

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

This will rank each number in the A column with NO duplicate ranks.
Each number will be ranked separately, like:

     A       B
 1) 33      10
 2) 45      9
 3) 46      8
 4) 49      7
 5) 60      5 <--
 6) 60      6 <--
 7) 68      4
 8) 69      3
 9) 85      2
10) 97      1

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 21, 2014 at 10:30:06
✔ Best Answer
This is a common question/issue regarding the RANK function. If the same number appears more than once, you have a tie and some Rank values are omitted.

Note what happens with the value 42 and the Rank value 4:


	 A	    B
1	50	    2
2	42	    3
3	36	    5
4	42	    3
5	52	    1

There are a number of ways to solve this issue.

Chip Pearson has a solution here:

http://www.cpearson.com/excel/Rank....

By using this formula you can eliminate any ties:

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

One possible issue with this method is that whichever duplicate value comes first in the list will always have the higher ranking. That can be "unfair" in e.g. an alphabetical list. If Barb and Sue both have a value or 42, Barb will always come first based on her name.

Another option that is a lot more random is to use 2 "Helper Columns" and the RAND function to create a unique random number to add to each value and then rank the Sum.

    A        B                 C                  D
1   50  =RAND()*.0001     =SUM(A1,B1)    =RANK(C1,$C$1:$C$5,0)
2   42	Drag Down to B5   Drag Down to C5   Drag Down to D5
3   36	   
4   42
5   52

RAND()*.0001 will create a very, very small positive number. With Excel's level of precision the odds of getting a duplicate number in 150 random values, and then having those duplicates align with the duplicates in your list is extremely small.

Now, do a Copy/PasteSpecial Values on those random number to lock them in place. If you allow them to change, then you have the possibility of the tied values changing their rank each time the sheet recalculates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question