Ranking without Repetitive Ranks
January 25, 2002 - by Juan Pablo Gonzalez
Here's a question I see often.
I'm trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in Column B. Is there a way I can get 5,4,2,3,1?
To do this you need a combination of RANK and COUNTIF as follows. If the data is in A2:A10, the formula in B2 would be:
and drag down till B10.
Extracted from MrExcel Message Board.