Microsoft Excel XP/X

Rank

The Rank function returns the rank of a value within a list. This function is useful when you need to maintain a sort based on another column and/or you want to determine the rank of items in more than one column.

This document will use the example of a gradebook which is sorted alphabetically by student last name. In the example, we want to find the rank of the Quiz 1 score of 47 for Chris Student. Scores on Quiz 1 ranged from 60 to 23.
Sample Gradebook image

Windows:

Open the desired worksheet
Select the cell where you would like the result of the function to appear
From the Insert menu, select Function...
OR
From the Formula bar, click INSERT FUNCTION
The Insert Function dialog box appears.
From the Or select a category pull-down list, select All or Statistical
From the Select a function scroll box, select RANK
Click OK
The Function Arguments dialog box appears.
In the Number text box, type the value that you want to know the rank of
EXAMPLE: Type 47
In the Ref text box, type the cell range to include
EXAMPLE: C2:C6
OR
1. Click COLLAPSE DIALOG
2. Select the cells to include
  A dashed border appears round the selected cells.
  EXAMPLE: C2:C6
3. Click RESTORE DIALOG
To obtain the rank of a value where the list of values is sorted in descending order, in the Order text box, type 0 or leave the text box blank
To obtain the rank of a value where the list of values is sorted in ascending order, in the Order text box, type any nonzero value
NOTE: This action does not sort the information in your worksheet, it is used only as a reference from which to perform the ranking.
EXAMPLE: To find the rank of 47 when the Quiz 1 scores are sorted from high to low, type 0
Click OK
The rank appears in the selected cell and the formula appears in the Formula bar.
EXAMPLE: Chris Student's score ranks 4th when the scores are sorted high to low (60, 54, 50, 47, 23).

Macintosh:

Open the desired worksheet
Select the cell where you would like the result of the function to appear
From the Insert menu, select Function...
OR
From the Standard toolbar, click PASTE FUNCTION
The Paste Function dialog box appears.
From the Function category scroll box, select All or Statistical
From the Function name scroll box, select RANK
Click OK
The Rank dialog box appears.
In the Number text box, type the value that you want to know the rank of
EXAMPLE: Type 47
In the Ref text box, type the cell range to include
EXAMPLE: C2:C6
OR
1. Click COLLAPSE DIALOG
2. Select the cells to include
  A dashed border appears round the selected cells.
  EXAMPLE: C2:C6
3. Click RESTORE DIALOG
To obtain the rank of a value where the list of values is sorted in descending order, in the Order text box, type 0 or leave the text box blank
To obtain the rank of a value where the list of values is sorted in ascending order, in the Order text box, type any nonzero value
NOTE: This action does not sort the information in your worksheet, it is used only as a reference from which to perform the ranking.
EXAMPLE: To find the rank of 47 when the Quiz 1 scores are sorted from high to low, type 0
Click OK
The rank appears in the selected cell and the formula appears in the Formula bar.
EXAMPLE: Chris Student's score ranks 4th when the scores are sorted high to low (60, 54, 50, 47, 23).