Join Dennis Taylor for an in-depth discussion in this video Ranking data without sorting, using the RANK function, part of Excel 2011 for the Mac: Advanced Formulas and Functions.
- In this worksheet, called Rank, we need to determine the order of the salaries. We can certainly do this by sorting the data. Simply click in column B there, select on the cells, do a descending sort to see these from high to low, or an ascending sort to see them from low to high. But, there's also a function called Rank. It comes in a few variations. If we simply use it in its basic form, the Rank function says, in effect, I've got a value somewhere, say it's in cell B2, let's compare that with all the values in column B.
This list has about 100 entries, slightly lower. If we simply use the Rank function in this situation here, like this, that's the 71st highest salary. I'm going to copy this formula down the column, by double clicking its field handle, in the lower right-hand corner. So within this list, the highest salary is 89,140. There's the ranking number one. Notice that right above this, we've got two salaries, they share the number two position, because they're equal to one another.
That means that in this list, there will not be a number three. That's how the rank function works. So when two are tied for position, the subsequent number that follows that will not be listed. Or, if there were three people on the list here, we might have three of them sharing the number two position, we would not see a number three or a number four, but then next entry would be a five. So in this list, we know for sure we're not gonna see a number three in the list. We'll possibly see a four a little bit later on, depending on how many we have. There's the four right there.
Maybe there's a three, didn't see it. So we might have three ties. Point is, that's how this works. There could be situations where the numbers you're comparing here, or you're trying to get a ranking for, need to be in the reverse order, from low to high. All we do with the rank function there, is follow the location reference with a comma and the number one. So the number one means reverse the order. Go from low to high. This is the 26th lowest salary. Let's double click the field handle to recopy this.
These previous high salaries now are the lowest. It's the 96th lowest, you could say here. And here, these two numbers that used to be tied for two, now their tied for 94th. So, we simply reverse the order of the ranking by putting in a one out here. If we put in zero, or simply put in nothing here, we are ranking these from high to low, as we saw previously. Now in a shorter list, sometimes these numbers pop out a little more easily. Let's do rankings here. I'll do these all at once, because we want to compare this with another function called rank.avg.
Here we use a simple rank. Since all the cells are highlighted, I'll use just this one, comma, compare with all the entries in column F, but I will press control return this time, so I'll have all these completed at once. And you can see the rankings. We have two 14s right here, another 14 right here, and another one down here. Of course they all have the same amount. It's 34. So that's the 14th highest entry in the list here. Now, rank average recognizes this kind of a situation and it comes up with different values.
It might surprise you. I haven't yet found a good application although I suspect there's some out there. Let's do a rank average here. We can see it popping up on the list. When you see a list like this, by the way, and sometimes with the longer name functions, it's more efficient, you can click this and it pops it into place. Here too, we are referring to that number, the number 34 over there in cell F2, comma, let's refer to the entire column here. And I'll press control return, and that certainly is a different answer. Double click and copy these down the column.
Some of these, of course, are going to be the same. The ones and twos are the same rate, right there. What's happening here, and why do we get this number? So once again, these two cells right here, and this one, and this one, all are tied for 14, using the standard rank function. But think of these also as occupying rank number 14, 15, 16, and 17. What's the average of 14, 15, 16, and 17? 15 and 1/2, and that's why we're seeing these entries here.
So again, I haven't found a good application for this, and yet we see how this is calculating averages in a different way. I think for most people, the standard rank function is exactly what you need. But just to point out, there are some variations in our function capability with certain kinds of functions like rank, that we're seeing right here.
- Using absolute, relative, and mixed references
- Using the Formula Builder
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Calculating payment
- Performing basic math
- Determining dates and times
- Editing text with functions
- Analyzing data with arrays