Excel: Ranking Number Range with Array Formula

 Last reviewed: November 30, 1994
Article ID: Q65398
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
  • Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0
  • Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

In Microsoft Excel, version 4.0, use the RANK function to rank a range of numbers according to their numerical value. To rank the numbers from small to large, where the small number is the first in rank, use the following function: 

   =RANK(firstcell,range,-1)
To rank them from large to small, use the following function: 

   =RANK(firstcell,range)
In Microsoft Excel versions prior to 4.0, to rank a range of numbers according to their numerical value from small to large or large to small, use one of the following array formulas, where "range" is the range of numbers to be ranked and "firstcell" is the first cell in "range": 

   =ROWS(range)-SUM(IF(range>=firstcell,1,0))+1
      (to rank from small to large)

   =ROWS(range)-SUM(IF(range>firstcell,0,1))+1
      (to rank from large to small)
To enter the formula, do the following:

  1. Type the formula into a cell that is adjacent to "range."

  2. Press COMMAND+ENTER (in Microsoft Excel for the Macintosh) or CONTROL+SHIFT+ENTER (in Microsoft Excel for Windows) to enter the formula as an array.

  3. Select the cell with the entered formula along with the same number of empty cells that are present in "range." For example, if "range" is A1 through A5, select B1 through B5, where B1 contains the formula.

  4. From the Edit menu, choose the Fill command appropriate for your row or column direction.

MORE INFORMATION

The following formulas will return the correct ranking of the numbers in cells $A$1:$A$5 (the braces {} indicate that the formula was entered as an array by pressing COMMAND+ENTER in Microsoft Excel for the Macintosh or CONTROL+SHIFT+ENTER in Microsoft Excel for Windows: 

   A1:  50   B1: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5>=A1,1,0))+1}
   A2:  12   B2: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5>=A2,1,0))+1}
   A3:  42   B3: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5>=A3,1,0))+1}
   A4:  31   B4: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5>=A4,1,0))+1}
   A5:   1   B5: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5>=A5,1,0))+1}
The values that are returned from the above formulas are as follows: 

   A1: 50    B1: 5
   A2: 12    B2: 2
   A3: 42    B3: 4
   A4: 31    B4: 3
   A5:  1    B5: 1

KBCategory: kbusage
KBSubcategory:

Additional reference words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00 2.01
2.2 2.20 2.21 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.