Consecutive Ranking
The following formulas return a consecutive 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 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
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