Transcripts for the above video clip:

RANK PROJECT

The RANK function allows you to rank cells or groups of cells in either ascending or descending order.

In this segment you will learn how to find and activate the function using the Function Wizard, and how to use the RANK function on a cell to either generate a ranking in its ascending order, or descending order

The RANK function can only be used to rank numeric cells; it cannot be used to rank alphabetical cells unless you use another function in Excel. What the RANK function is very useful for, is determining for example who is the best and worst in a situation, and their rankings in between.

So for example we have a list of Sales People here, we have how many Units they’ve sold of a product, and as result of the average price they’ve achieved there’s a Total Sales value. And what we’d like to do is see who is the best, worst, and their rankings in between in terms of Units Sold, and likewise the best, the worst, and the rankings in between for the Total Sales value. To do this you need to do the following;

• click in the cell where you want the information displayed,
• click on the Function Wizard
• now you need to find the RANK function, so what you can do is either go to its subcategory which is Statistical,
• click inside the box
• push R
• and RANK will come up
• or else you can go to All the functions
• again click in the box
• push R
• and find the RANK feature
• click Ok,

Now it asks you for a bit of details to help rank these numbers,

• the first one is the Number and it wants to know what number is being ranked, so in this case we want to rank Adrian’s Units Sold so you click on that
• the next criteria is the Reference and what its asking is, this number you’ve given me what must I compare it against, and in this case I want to highlight all the Units Sold
• now because the chances are very good you are going to copy and paste this formula across, I highly recommend you anchor this by putting the dollar signs in. And you can push F4 to do it quickly,
• the last criteria is in what order must it rank it in, is it a descending or ascending order, and as it shows you down here if you want it in descending order you type a 0 or you can omit it
• and if you want an ascending order you can put any none 0 value for example 1
• in this case we want to get it in descending order; we want to see whose first and whose last, who sold the most. So we type a 0 in there
• and you push Ok,

What you’ll see is it tells you immediately that Adrian’s 78 Units Sold is ranked 3^rd which makes sense in terms of Jane has got 89, and Trevor had 81.

• Now you can quite easily copy
• and paste this all the way down
• and what you quickly get is the rankings of the various Sales People
• So you’ll see quickly that Jane is number 1 with 89
• 2^nd is Trevor on 81
• and 3^rd is Adrian on 78,
• what is interesting is you’ll see Mary and Cary are both on 67 and the way Excel handles this is by putting them both at 4^th
• and you’ll see that then there is no 5^th place it goes immediately to 6^th and then there’s 7^th

And that gives you a good quick ranking of the Units Sold.

Now we can do the same to RANK the Sales People by Total Sales values, so again

• you click in the cell you want
• I recommend you use the Function Wizard
• you find the RANK feature,
• this time the Number you comparing is the Total Sales values so you point to that
• you want to compare it against the other Total Sales values so you just highlight that section
• again I recommend you anchor it by pushing F4 or putting the dollar signs in
• the order again is going to be descending order, so we going to type in a 0
• you click Ok
• and you’ll see that Adrian again is 3^rd in Total Sales value
• you can copy it down

And now you have quickly and automatically performed rankings, which give some interesting statistics. For example Antoinette although she was the worst in terms of Unit Sold is the best in Sales value because her average sales price was the best.

Now for example what’s nice about the RANK feature is let’s say we were actually wrong

• and this was actually should have been 100
• when you click enter
• automatically the rankings are redone

Now Antoinette is the top in Units Sold and everyone else has been adjusted down.