Excel is loaded with many functions that you may not be aware of.
You can use RANK to automatically rank your results.
The exercise and the result are both in the Rank file.
Here is our data. Let's rank the salespeople.
Here is the ranked result:
The formula is =RANK(B2,sales). There are three parts:
What cell are you ranking. In this case, cell B2, the sales results for Smith.
What range are you referencing. Here, it is the named range, "sales', which includes all the sales results.
Is it ascending or descending order? If you leave it blank or the number 0, as we did here, it would be descending order. Put in any other number than 0, and the sort would be in ascending order.
Here is the ascending order version. Same formula, except for the change at the end: =RANK(B2,sales,1)
Now let's get nitpicky and fix it with a very advanced formula. What about ties? Notice in the descending rank, we had two people with the same sales and ranked them 7. But suppose you say, well they tied, so let's adjust the ranking.
We will make you happy and throw in a "T" beforehand with a little trick. Here is the result:
Here is the formula- {=IF(SUM(1*(B8=sales))>1,"T" & RANK(B8,sales),RANK(B8,sales))}
Let's break it down:
B8=sales. Here is the tricky part. What we do is look at the sales value in cell B8 and then look for how many times that value appears in the sales range (in other words, how many other reps had the same sales amount).
1*(B8=sales). This assigns a value of one for each match (i.e. tie) with that sales value.
SUM(1*(B8=sales)). Then we add up all the ties.
=IF(SUM(1*(B8=sales))>1. We then test to see if there were any ties. If there were not, then the formula would have just a value of 1.
"T" & RANK(B8,sales). What we do when the IF statement is true. When there is a tie, we add a "T" in front. The second part is the rank.
RANK(B8,sales). What we do when the IF statement is false. When there is not a tie, we just show the rank.
Once again, with the advanced formulas, even if you don't fully understand it, you have it on the web via our website and it is yours to use to cut and paste.
Two pointers to make it work:
Remember we use an array in the formula, so save it clicking the Ctrl, Shift and Enter keys at the same time.
When you first enter the formula, the ones with ties will be left adjusted. Change the formatting to right adjust.
Rank is a neat function in Excel to show automatically how certain values rank. Enjoy.