Microsoft KB Archive/213916

= HOW TO: Rank Duplicate Values Sequentially in Excel 2000 =

Article ID: 213916

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213916



For a Microsoft Excel 98 and earlier version of this article, see 152567.

IN THIS TASK
SUMMARY Rank Duplicate Values
 * Example 1: Ascending Order in a Column
 * Example 2: Descending Order in a Column
 * Example 3: Ascending Order in a Row
 * Example 4: Descending Order in a Row



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  is the relative address of the cell containing one of the values to be ranked, and   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.

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

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:   Create the following worksheet: <pre class="fixed_text">  A1: 100     B1: A2: 75     B2: A3: 100    B3: A4: 75     B4: A5: 50     B5: </li> 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))))

</li> Press CTRL+SHIFT+ENTER.</li>  Select cell B1, grab the fill handle, and then fill the formula down through cell B5. The ranked values appear as follows: <pre class="fixed_text">  A1: 100     B1: 1 A2: 75     B2: 3 A3: 100    B3: 2 A4: 75     B4: 4 A5: 50     B5: 5 </li></ol>

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:   Create the following worksheet: <pre class="fixed_text">  A1: 100  B1: 75  C1: 100  D1: 75  E1: 50 A2:     B2:     C2:      D2:     E2: </li> 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))))

</li> Press CTRL+SHIFT+ENTER.</li>  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: <pre class="fixed_text">  A1: 100  B1: 75  C1: 100  D1: 75  E1: 50 A2: 4   B2: 2   C2: 5    D2: 3   E2: 1 </li></ol>

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:   Create the following worksheet: <pre class="fixed_text">  A1: 100  B1: 75  C1: 100  D1: 75  E1: 50 A2:     B2:     C2:      D2:     E2: </li> 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))))

</li> Press CTRL+SHIFT+ENTER.</li>  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:

<pre class="fixed_text">  A1: 100  B1: 75  C1: 100  D1: 75  E1: 50 A2: 1   B2: 3   C2: 2    D2: 4   E2: 5 </li></ol>

back to the top

Keywords: kbhowto kbhowtomaster KB213916

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.