Unique Ranking
The formulas in the following examples return a unique ranking for a number in a list of numbers. In the first example, the numbers are ranked in descending order, whereas the numbers in the second example are ranked in ascending order.
Rank In Descending Order
The following formula, entered in B2 and copied down, returns the unique ranking in descending order for the values in A2:A10...
=RANK(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1
Based on the sample data, the formula returns the values in B2:B10.
Sample Workbook: Download
Rank In Ascending Order
The following formula, entered in B2 and copied down, returns the unique ranking in ascending order for the values in A2:A10...
=RANK(A2,$A$2:$A$10,1)+COUNTIF($A$2:A2,A2)-1
Based on the sample data, the formula returns the values in B2:B10.
Sample Workbook: Download