From Ted French,
Your Guide to Spreadsheets.
FREE Newsletter. Sign Up Now!

The RANK function, one of Excel's statistical functions, ranks the size of a number compared to other numbers in a list a data.

The syntax for the RANK function is:

= RANK ( Number, Ref, Order )

Number - the cell reference of the number to be ranked.

Ref - the range of cells to use in ranking the Number.

Order - determines whether the Number is ranked in ascending or descending order.

Type a "0" (zero) to rank in descending order (largest to smallest). Type a 1 to rank in ascending order (smallest to largest).

Example Using Excel's RANK Function:

Note: For help with this example, see the image to the right.

1. Enter the following data into cells D1 to D6: 123,135,147,130,98,187.
   
   
2. Click on cell E1 - the location where the results will be displayed.
   
   
3. Click on the Formulas tab.
   
   
4. Choose More Functions > Statistical from the ribbon to open the function drop down list.
   
   
5. Click on RANK in the list to bring up the function's dialog box.
   
   
6. Click on cell D2 to choose the number to be ranked (135).
   
   
7. Click on the "Ref" line in the dialog box.
   
   
8. Drag select cells D1 to D6 in the spreadsheet to enter the range into the dialog box.
   
   
9. Click on the "Order" line in the dialog box.
   
   
10. Type a zero on this line to rank the number in descending order.
    
    
11. Click OK.
    
    
12. The number 3 should appear in cell E1 since the number 135 is the third largest number.
    
    
13. The complete function = RANK ( D2 , D1 : D6 , 0 ) appears in the formula bar above the worksheet when you click on cell E1.

Related Articles

• Excel 2007 SMALL Function
• Excel 2007 LARGE Function