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