This step-by-step 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.
If a row or column of cells in a Microsoft Excel worksheet contains duplicate values, you can use the RANK function to assign the same rank value to every occurrence of the duplicate value. The presence of duplicate numbers affects the ranks of subsequent numbers. For example, if the number 10 has the rank value of 5, and the number 10 appears twice, there is no number with the rank of 6, but there can be a number with the rank of 7.
Rank Duplicate Values
You can assign a unique rank for all numbers in a range by using 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 that contains 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 that are listed in
ascending or descending order in a column or row.
Example 1: Ascending Order in a Column
To modify and use the formula to rank values in a column in ascending order, follow these steps:
- Start Excel, and then create the following 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))))
- Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
- Select cell B1, grab the fill handle, and then 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
Example 2: Descending Order in a Column
To modify and use the formula to rank values in a column in descending order, follow these steps:
- Create the following 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))))
- Press CTRL+SHIFT+ENTER.
- Select cell B1, grab the fill handle, and then 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
Example 3: Ascending Order in a Row
To modify and use the formula to rank values in a row in ascending order, follow these steps:
- Create the following 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,1,INDEX(COLUMN(A1)-COLUMN($A$1)+1,1)-1))))
- Press CTRL+SHIFT+ENTER.
- With cell A2 selected, grab the fill handle, and then fill the formula to the 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
Example 4: Descending Order in a Row
To modify and use the formula to rank values in a row in descending order, follow these steps:
- Create the following 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,1,INDEX(COLUMN(A1)-COLUMN($A$1)+1,1)-1))))
- Press CTRL+SHIFT+ENTER.
- With cell A2 selected, grab the fill handle, and then fill the formula to the 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