10. Ranking Metrics
To rank our sales managers in terms of performance, we'll need use the RANK() function for each of our sales team metrics.
Ranking numbers in Excel
- Use the RANK() function to rank a number within an array of numbers
--- number: The value you wish to rank
--- ref: The total array of values
--- [order]: Optional argument that so you specify an ascending/descending order
SHIFT + →: Select next cell
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
F2: Jump back inside a formula
CTRL + SHIFT + →: Select all cells within data region
The last remaining piece of analysis for our dashboard will rank the selected sales manager along our three metrics. Revenue, new revenue rate and lapsed revenue rate. To complete this task, we'll use the Rank function. The rank function accepts a value and then ranks that value within an array of other values. So for example to rank the revenue of the sales manager Moll, we select the revenue for Moll and then rank that revenue among all revenues for the 15 sales managers. So I'll go back to my dashboard and write equals rank. I'll then open a bracket and the number will be Moll�s revenue. The reference will then be the array. So going to my dashboard input sheet and selecting the 15 sales manager array, closing the bracket and press Enter. And this tells me that Moll has a revenue rank of 5. If I look across at the chart to the left, our sales managers are ordered by revenue I can see that this number is correct. I can repeat this for new revenue rate. So again I'll use the rank function. Open the bracket, the number will be the new revenue rate for Moll. The ref will be the new revenue rate array for all 15 sales managers. I'll select with Ctrl + Shft + down arrow, close the bracket and press Enter. And this tells me that Moll�s new revenue rate is very high in fact it's the second highest among all sales managers. And finally for the lapsed revenue rate, I'll use rank one more time.
So again the number will be Moll�s specific rate, the ref will be the full array and as before we can close the bracket. Rank by default orders the largest number in an array number 1. The second largest number 2 etcetera. So the default order is descending. However for lapsed revenue rate the lower the value the better. So I need to reverse the order by which rank is calculated.
To do this I'll jump back into formula with F2 and if I enter a (,) after last value you'll see that we have optional value of order which can be specified as descending or ascending. I'll type 1 for ascending and close the bracket. And this now changes Moll�s rank from 10 to 6, which is correct. Whenever you're using the rank function always be sure that you select the correct order of ascending or descending depending on the metric that you are measuring. While it's nice to have the rank number under each title, I'd like to know how many sales managers in total are in the ranking. To do this I'll add a string on to the value. So I'll press F2 to jump back into the formula, I'll use the & sign so I can add text to the string.
I'll open inverted commas, I'll write a space and then say, out of 15, which is the total number of sales managers.
I'll close the inverted commas and press Enter. And this looks much better than having the bare ranking on its own. Off camera, I'll complete this change for the other two rankings.
These rankings can serve to tell you much more about sales manager performance than the chart on the left-hand side.
Let's take for example Batiste, who's the second highest revenue of all sales managers.
Given this position you might assume that Batiste had a very strong sales year in 2013. However, this is not the case. Last year his ability to keep customers was in the lower half of the group. And his ability to attract new customers was even worse coming in third last overall. Batiste higher revenue in 2013 was in fact due to a stellar year in 2012 and since then he's been relying heavily on his existing customers to renew their business this year. If we had not calculated these rankings, however, we would never have known this.
As this lesson has hopefully shown you, rankings are very effective when you want to compare metrics within a group. be it customers, sales managers or even geographic regions.
They are also very easy and quick to create and should always be considered when you're building a dashboard.