RANK function

Paul Figler sent me a tweet, asking for instructions on the RANK function.
Paul is co-Founder of whlstats.ca Inquiries at p.figler@whlstats.ca.

You have a list of numbers. You want to know the highest to lowest but you can't sort by that number field. You need to sort by another field. 

How do we illustrate how each number relates to the others in the list? 

Conditional Formatting is one way. It adds Color to the cell. See the blog on it.

Another way is to use the RANK function. 

The function is laid out as the following: 

=RANK(cell you want to rank, cell range of numbers being ranked, order)

Order is 0 (or blank) or 1. 

0 means descending order. 9-0

1 means ascending order. 0-9

1. Type in the table with all the data, including the numbers in the column you want to rank. 

    In the example below, the numbers I want to rank are in cells C10 to C20 

3. Click on the first cell of the column you want the ranking numbers to appear.
     In this example click in D10. 

4. Type in the following formula using the RANK function 

=RANK(C10,C10:C20,0)

5. The answer is the rank of the number in C10 in comparison to the numbers in C10 through C20. 

Now we need to rank the rest of the numbers by copying the formula down the column.

Fill down the numbers 
As we copy the number down the column C10 will change to the next row because it is a relative cell address. 

C10:C20 will also change, which we do not want unless we make them an absolute cell address. 

Absolute Cell Address
1. Click in D10.

2. In the formula bar, click before the C10 of C10:C20 so that your cursor is blinking just before the C.

3. Click on the function key, above the number on the keyboard, F4.
    This inserts a $ dollar sign before the C and before the 10 of C10.

4. Click before the C of C20.

5. Press F4 again.
    The $ dollar signs now appear before the C and the 20 of C20.

    The formula looks like the following:
    =RANK(C10,$C$10:$C$20,0)

6. Press ENTER.


Now we want to copy this function down the column. 

1. Click in C10.

2. Click on the Fill Handle in the bottom right-hand corner of the cell. 

3. Drag the Fill Handle down to D20 and then let go. The formula has been copied all the way down my table. 

Named Ranges

To make this a little easier we can name the cells that have the numbers to be ranked.

1. Highlight the cells to be ranked. 

    In this example C10:C20

2. Right-click over the selected cells.

3. Select Name Range near the bottom of the contextual menu. 

4. Name the range RankNumbers

The formula now would be: 

=RANK(C10,RankNumbers,0)