You are on the Home/Excel/Tutorials/Ranking page Share Your

### Ranking

Excel's RANK function ranks a number within a given range of numbers.  The syntax is

RANK(number,ref,order)

Number   is the number whose rank you want to find.

Ref   is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

Order   is a number specifying how to rank number.

If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order

As intended, RANK gives the same rank to duplicates and resumes ranking for subsequent numbers after leaving a gap.  For example, consider a scenario where numbers are ranked in descending order.  If the number 10 appears twice in a list with a rank of 5, the next number, say 9, would have a rank of 7.  Similarly, since 9 appears 3 times, the next number, 8, has a rank of 10.  The formula in C3: =RANK(B3,\$B\$3:\$B\$12)  Copy C3 as far down as there are data in column B. There are instances when this result is not acceptable.  This page looks at three ways to modify the result of Excel's RANK function.

To simplify the formulas, create two names (Insert | Name > Define...)

The first refers to the original data range, the other to the adjacent cells containing the unmodified RANK formula.

DataRng =Sheet1!\$B\$3:\$B\$12
RankRng =OFFSET(DataRng,0,1)

 Adjust a tie so that it reports the "middle" value.In the first cell containing the rank, enter the formula =RANK(B3,DataRng)+(COUNTIF(DataRng,B3)-1)/2 Copy as far down as there are data in DataRng Note that the Excel help file recommends the more complex correction factor =(COUNT(ref) + 1 � RANK(number, ref, 0) � RANK(number, ref, 1))/2 Use a tie breaker to create a unique rank In the first cell containing a rank, enter the formula =RANK(B3,DataRng)+COUNTIF(\$B\$3:B3,B3)-1 Copy as far down as there are data in DataRng. A variant of the tie break that assigns ranks in reverse order In the first cell containing a rank, enter the formula =RANK(B3,DataRng)+COUNTIF(DataRng,B3)-COUNTIF(\$B\$3:B3,B3) Copy as far down as there are data in DataRng. Retain duplicate values but create continuous ranks, i.e., no breaks in the rank. In the first cell containing a rank, enter the array formula1 =SUM(1/(IF(RankRng

References: See Chip Pearson's http://www.cpearson.com/excel/rank.htm