The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95, versions 7.0, 7.0a
- Microsoft Excel for Windows, versions 5.0, 5.0c
- Microsoft Excel for the Macintosh, versions 5.0, 5.0a
SUMMARY
If a row or column of cells contains duplicate values, the RANK function
assigns the same rank value to every occurrence of the duplicate value.
This article describes a formula that you can use to assign a unique rank
for all numbers in a range, even if the range includes duplicate values.
MORE INFORMATION
To assign a unique rank for all numbers in a range, use the following
formula:
=SUM(1*Cell>=Range))-(SUM(1*(Cell=Range))-1)/2
where "cell" is the relative address of the cell containing one of the
values to be ranked, and "range" is the absolute address of the range
containing all of the values. This formula assigns a unique rank to every
value in a range, in ascending order.
By modifying this formula, you can rank values which are listed in
ascending or descending order in a column or row.
Values in a Column, Ascending Order
- Type the following data in a worksheet:
A1: 100 B1:
A2: 75 B2:
A3: 100 B3:
A4: 75 B4:
A5: 50 B5:
- In cell B1, type the following formula:
=SUM(1*(A1>$A$1:$A$5))+1+IF(ROW(A1)-ROW($A$1)=0,0,
SUM(1*(A1=OFFSET($A$1,0,0,INDEX(ROW(A1)-ROW($A$1)+1,1)-1,1))))
NOTE: The formula must be entered as an array formula. To enter a
formula as an array formula in Microsoft Excel for Windows, press
CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press
COMMAND+ENTER.
- With cell B1 selected, grab the fill handle and fill the formula down
through cell B5.
The ranked values appear as follows:
A1: 100 B1: 4
A2: 75 B2: 2
A3: 100 B3: 5
A4: 75 B4: 3
A5: 50 B5: 1
Values in a Column, Descending Order
- Type the following data in a worksheet:
A1: 100 B1:
A2: 75 B2:
A3: 100 B3:
A4: 75 B4:
A5: 50 B5:
- In cell B1, type the following formula:
=SUM(1*(A1<$A$1:$A$5))+1+IF(ROW(A1)-ROW($A$1)=0,0,
SUM(1*(A1=OFFSET($A$1,0,0,INDEX(ROW(A1)-ROW($A$1)+1,1)-1,1))))
NOTE: The formula must be entered as an array formula. To enter a
formula as an array formula in Microsoft Excel for Windows, press
CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press
COMMAND+ENTER.
- With cell B1 selected, grab the fill handle and fill the formula
down through cell B5.
The ranked values appear as follows:
A1: 100 B1: 1
A2: 75 B2: 3
A3: 100 B3: 2
A4: 75 B4: 4
A5: 50 B5: 5
Values in a Row, Ascending Order
- Type the following data in a worksheet:
A1: 100 B1: 75 C1: 100 D1: 75 E1: 50
A2: B2: C2: D2: E2:
- In cell A2, type the following formula:
=SUM(1*(A1<$A$1:$E$1))+1+IF(COLUMN(A1)-COLUMN($A$1)=0,0,
SUM(1*(A1=OFFSET($A$1,0,0,INDEX(COLUMN(A1)-COLUMN($A$1)+1,1)-1))))
NOTE: The above formula must be entered as an array formula. To
enter a formula as an array formula in Microsoft Excel for Windows,
press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press
COMMAND+ENTER.
- With cell A2 selected, grab the fill handle and fill the formula
right through cell E2.
The ranked values appear as follows:
A1: 100 B1: 75 C1: 100 D1: 75 E1: 50
A2: 4 B2: 2 C2: 5 D2: 3 E2: 1
Values in a Row, Descending Order
- Type the following data in a worksheet:
A1: 100 B1: 75 C1: 100 D1: 75 E1: 50
A2: B2: C2: D2: E2:
- In cell A2, type the following formula:
=SUM(1*A1<A1:E1))+1+IF(COLUMN(A1)-COLUMN(A1)=0,0,
SUM(1*(A1=OFFSET(A1,0,0,1,INDEX(COLUMN(A1)-COLUMN(A1)+1,1)-1))))
NOTE: The above formula must be entered as an array formula. To
enter a formula as an array formula in Microsoft Excel for Windows,
press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press
COMMAND+ENTER.
- With cell A2 selected, grab the fill handle and fill the formula
right through cell E2.
The ranked values appear as follows:
A1: 100 B1: 75 C1: 100 D1: 75 E1: 50
A2: 1 B2: 3 C2: 2 D2: 4 E2: 5
|