# 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