You are currently offline, waiting for your internet to reconnect

HOW TO: Rank Duplicate Values Sequentially in Excel 2000

This article was previously published under Q213916
SUMMARY
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.

back to the top

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 thevalues 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 everyvalue in a range, in ascending order.

By modifying this formula, you can rank values that are listed inascending or descending order in a column or row.

back to the top

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:
  1. Start Excel, and then create the following worksheet:
       A1: 100     B1:   A2:  75     B2:   A3: 100     B3:   A4:  75     B4:   A5:  50     B5:					
  2. 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))))
  3. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
  4. 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					
back to the top

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:
  1. Create the following worksheet:
       A1: 100     B1:   A2:  75     B2:   A3: 100     B3:   A4:  75     B4:   A5:  50     B5:					
  2. 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))))
  3. Press CTRL+SHIFT+ENTER.
  4. 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					
back to the top

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:
  1. Create the following worksheet:
       A1: 100  B1: 75  C1: 100  D1: 75  E1: 50   A2:      B2:     C2:      D2:     E2:					
  2. 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))))
  3. Press CTRL+SHIFT+ENTER.
  4. 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					
back to the top

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:
  1. Create the following worksheet:
       A1: 100  B1: 75  C1: 100  D1: 75  E1: 50   A2:      B2:     C2:      D2:     E2:					
  2. 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))))
  3. Press CTRL+SHIFT+ENTER.
  4. 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					
back to the top
Properties

Article ID: 213916 - Last Review: 09/27/2003 14:00:12 - Revision: 3.1

  • Microsoft Excel 2000 Standard Edition
  • kbhowto kbhowtomaster KB213916
Feedback