HAPPY FRIDAY EXCELLERS…. let’s look at Ranking today.
The RANK function will compare numbers to other numbers contained in an Excel list. So if you apply RANK to a list of numbers, Excel will apply a ranking number to them either in an ascending order or a descending order.
There a a few parts or arguments to the function.
=Rank( number, array, [order] )
number – this is the number to find the rank for
array – this is the range or array of number to use for ranking
order– this is an optional argument and it specifies how to rank your numbers
If you set the order to 0 the numbers will be ranked in descending order
If the order is not 0 then the numbers are ranked in ascending order
If the order argument is not supplied then if defaults to 0 (descending order)
So let’s take a look at an example. I recently had to generate a league table for sales representatives, the league is ongoing based on their value sales which will run throughout the summer months. This seemed to be the ideal scenario to use RANK.
Here is my data, with May 2016 already filled in as those sales figures have been confirmed.
My league table consisted of 10 sales people in the department. They are to be ranked based on their sales volumes, from 1 to 10. 1 being the top ranking and 10 the bottom. They are to be ranked over a 3 month period, May June and July.
I have added a column into the league which gives a total of all three months which I will base my ranking on, this way I only need to use the Total Sales column to use for ranking values. So as we have the first months data let’s see how the Sales people are doing in their ranking up to now. We can start by entering the =RANK function.
We want to start with the first number we want to rank which is in cell reference E2- this is the number argument of the function
We then need to select the full data range of which to rank the number – $E$2:$E$11 – this is the ref argument of the function (this range of cells I make absolute as we will be dragging the formula down our list of values.)
I want the ranking to be in descending order, so I have set the [order]argument of the formula to 0. ( The highest sales will be ranked 1 and the lowest 10).
Once entered I can drag the formula to the full list cells. The Sales people are now ranked for May 2016 and Scott Chalmer is in the lead. Well Done Scott!!!.
Once I enter the sales figures for June and then July the formula will automatically update the the ranking of the Sales people.
If you want to download the sample workbook to work through the example you can do so here.
Formula Friday - Excel Ranking Function
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
- Use TRIM for a speedy data clean up
