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<C3,COUNTIF(RankRng,RankRng),9.999999999E+307)))+1
Copy as far down as there are data in
DataRng.
Accomplish the same with the new
IFERROR function introduced with Excel 2007.
In the first cell containing a rank, enter
the array formula1
=SUM(IFERROR(1/COUNTIF(RankRng,IF(RankRng<C3,RankRng)),0))+1
Copy as far down as there are data in
DataRng.
1 To enter an array
formula complete data entry *not* with the ENTER key but the
CTRL+SHIFT+ENTER combination. |
 |
