Excel: Ranking Number Range with Array Formula

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:

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

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.

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.

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&gt;=A1,1,0))+1}
   A2:  12   B2: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5&gt;=A2,1,0))+1}
   A3:  42   B3: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5&gt;=A3,1,0))+1}
   A4:  31   B4: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5&gt;=A4,1,0))+1}
   A5:   1   B5: {=ROWS($A$1:$A$5)-SUM(IF($A$1:$A$5&gt;=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

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00 2.01 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :