HOW TO: Rank Duplicate Values Sequentially in Excel 2000

Article translations Article translations
Article ID: 213916 - View products that this article applies to.
This article was previously published under Q213916
Expand all | Collapse all

On This Page

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.

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:
  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
    					

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
    					

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
    					

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
    					

Properties

Article ID: 213916 - Last Review: September 27, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowto kbhowtomaster KB213916

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com