Consecutive Ranking
The formulas in the following examples return a consecutive 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 consecutive ranking in descending order for the values in A2:A10...
=SUM(IF(A2<$A$2:$A$10,1/COUNTIF($A$2:$A$10,$A$2:$A$10)))+1
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
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 consecutive ranking in ascending order for the values in A2:A10...
=SUM(IF(A2>$A$2:$A$10,1/COUNTIF($A$2:$A$10,$A$2:$A$10)))+1
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns the values in B2:B10.
Sample Workbook: Download