Microsoft KB Archive/152567

= How to Rank Duplicate Values Sequentially =

Article ID: 152567

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q152567





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 that contains 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.

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: <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)))) 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. </li> With cell B1 selected, grab the fill handle and fill the formula down through cell B5.</li></ol>

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

Values in a Row, Ascending Order
  Type the following data in a 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)))) 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. </li> With cell A2 selected, grab the fill handle and fill the formula right through cell E2.</li></ol>

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

Values in a Row, Descending Order
  Type the following data in a 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)))) 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. </li> With cell A2 selected, grab the fill handle and fill the formula right through cell E2.</li></ol>

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

Additional query words: 5.00a 5.00c 97 8.00 XL97 XL98

Keywords: kbhowto KB152567

-

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

© Microsoft Corporation. All rights reserved.