|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:
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:
By using this formula you can eliminate any ties:
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
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