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

✔ 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 1There 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 52RAND()*.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

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?

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) 45Then 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 9Where column B lists the Rank of the value in A

within the whole group of 10 values.Is that what you want?

MIKE

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 1MIKE

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 1There 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 52RAND()*.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

Ask Your Question

Weekly Poll

Would you be willing to go on a SpaceX trip around the moon?

Discuss in The Lounge

Poll History