Microsoft KB Archive/65398

{|
 * width="100%"|

Excel: Ranking Number Range with Array Formula

 * }

-

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 &quot;range&quot; is the range of numbers to be ranked and &quot;firstcell&quot; is the first cell in &quot;range&quot;:

  =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 &quot;range.&quot;
 * 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 &quot;range.&quot; For example, if &quot;range&quot; 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&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 :

Technology : kbvcSearch