Unique Ranking
The following formulas return a unique ranking for a value in a list of values. The formula in the first example ranks the values in descending order, whereas the formula in the second example ranks them in ascending order.
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
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