Join Dennis Taylor for an in-depth discussion in this video Ranking data without sorting with RANK, part of Excel 2010: Advanced Formulas and Functions.
- View Offline
Here is a worksheet called Rank. And in columns A and B, we see Employee Names and Salary. We would like to know the rank of each salary compared to the entire list. We can easily do this by sorting, but there are times when you don't want to sort. We want to keep this list in alphabetical order, but we do want to know the ranking here. In Excel 2010, the RANK function, which has been around for a while, is still available, but there are two variations on it. For compatibility reasons, Excel has kept--and will keep--the Rank function.
But as I'm typing this, notice, what also we are seeing here on the pop-up tip. There is a choice called RANK.EQ. It's the same as RANK. You can use either one. If, however, you are in an environment where people are using Excel 2007, you probably want to continue these RANK. But they work the same way. RANK.EQ returns the rank of a number in a list of numbers, its size relative to the other values. If more than one value has the same rank, the top rank of that set of values is returned.
I think rather than worrying about what that really means, let's show you what it means. So we use for RANK.EQ or RANK. In this case, I will just choose RANK. What are we looking at first? Cell B2, comma, compared with the entire list in column B. Notice the word "order" out here. We are going to ignore that. When we do ignore this, you will see how this works. It works in what we call a descending order. This is the 71st highest salary. Double-click to copy this down the column. You can see here, here's the highest salary.
It has the order of 1. Notice that two salaries are identical here. They are sharing the second place, and that means that in this list here of about 96 entries, we do not have a rank of 3. These you could say occupy the 2nd and 3rd slot. The next ranking we would see in order here would be 4. We might temporarily see how this plays out, just by sort by salary. Click here and I will simply click ZA on the Data tab here to sort these in descending order.
Now remember, we don't need to do this, sometimes we cannot do this, but I want to expose the salary rank column in a slightly different way. And there I think you can see what's happening: the 1, two 2s, the 4, and so on, and two 6s as well, but no 7, so they are sharing the 6th and 7th position. Some people prefer to show this in a different way. I am going to use this column here to show you a variation on RANK, and that's the one we will saw but didn't talk about: RANK.AVG. If more than one value has the same rank, the average rank is returned.
Let's choose this variation here. And the number we are looking at again is the entry out of column B. Comma. We'll put B here, and we will just complete that. That's 1. Now, we might have to change the display here. Let's go the Home tab, choose the comma button here, and perhaps general format. I am going to press Ctrl+Shift+Tilde to make this general format even more revealing. You see the difference here. This is using RANK.AVG. Here's the more standard use of RANK.
So if you want it like this, there we go. Since these are sharing the second and third positions, those numbers are average. The average of 2 and 3 is 2.5. We see that's being displayed there. It happens here too. Four of these entries share the 11th position. This is how it looks using RANK or RANK.EQ. This is how it looks with RANK.AVG. You can also reverse the order of these in all cases here by putting in the third argument. So in this example right here, if we put in comma, you'll notice the prompt here: "Rank numbers as if reference were sorted in descending order," 0 or blank that's what we saw earlier is Descending. Let's choose Ascending order here. Let me just either tab in it or type a non- zero entry and then re-copy this, and you see what's happening here.
And so this which had been first, now is at the very bottom. So depending upon the kinds of numbers that you are looking at and the information you are looking at, I think you want to experiment a bit with the order here. Now again, we emphasize at the beginning here there are certainly times when you do not want to sort the data. So I am going to put this data back in the order that it had been, and we could imagine having done everything that we did here without ever sorting the data. But it did bring it up temporarily. Sometimes when you're experimenting with this too, it might be easier to--if you don't use it very frequently--to kind of get your bearings on this, work with the smaller lists say lists like we are seeing over here and again experiment with RANK and RANK.AVG. And with the smaller list, I think you'll have a better sense of how it might work.
But there is no question that this is a valuable function for determining the rank of numbers from a list, and potentially a huge list as well.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references