Rank data with the RANK and RANK.AVG functions

- [Instructor] If we wanted to rearrange this list in columns A through E based on salary, we could certainly sort the list and see how the salaries rank. But sometimes we want to keep the list in a certain order. We're concerned about other data here. We want the ranking to always be part of the record. And the data will change. So imagine the salaries will change. But how do we calculate the rank here? Well, fortunately, we don't really have to calculate it. We've got a function. And as we start to type rank, you'll notice that there are actually three functions. RANK and RANK.EQ are identical.

Probably in future versions of Excel, there will no longer be a RANK but there will be a RANK.EQ. Let's use this one. The salary in question is the one right here in E2, comma, and we're comparing that with other entries. And where are the other entries? They're all in column E. Now this looks like it could be an illegal reference 'cause we're overlapping, but it isn't. Now I can press Enter and get an answer. But you'll notice there's another option out here. If I put in comma, you see it pops up. I want these in descending order or ascending order.

In other words, do I want to rank these from the top? It looks like this one might be near the top. We're not sure if it's the top one, but we can put in zero or just ignore it. In other words, don't put in the comma. That's actually the third highest. And we'll double-click to copy this down the column. We've got our other answers. It looks like we've got another third. Notice when two of the entries are the same, they share the same position. Now if you were to look up and down this list, you will not find a four. Here's a five, and actually, these two positions here, you could say they share the third and fourth position.

Here's the fifth position. Now if we wanted to rank these in reverse order, bottom-up, you saw the option there. After putting in the location here, put in the comma and put in the one. Now with this order, and I'll double-click. And there we've got the 240 and this one down here is the 240. So these are from the bottom, starting form the bottom coming upward, these are near the top of the list that way. So different approaches. I'm going to press control + z because I think it's more common to see these in the ascending order.

Now RANK.AVG, I haven't seen a really good use of this, but you want to be alert to it. It does something a little bit different. And I can actually just copy this to the right and adjust the cell reference. I want this to be referring to cell E2. And instead of column F, it's column E. But instead of EQ here, AVG returns the rank of a number on a list of numbers, its size relative to other values in the list, on and on and on. That's not exactly obvious, but let's use it and see what it does.

Three and a half, interesting. How about the next one? Drag it down a few. That's going to be the same, that's going to be the same. Most of these are going to be the sme. But look what we see here. These are threes. Now remember earlier I had said three and three share the third and fourth position. What's the average of those two positions, the third and fourth? Well, it's three and a half. That's why we're seeing this number. Now again, what do you do with this I'm not exactly sure. If we adjust another salary here and make it be third as well, I'll take this 128 down here, I'm going to make this salary be 130,229.

I'll simply copy this one downward, dragging with the control key, and put it right there. What are we going to get now? Depending on how we're calculating rank, we're going to get some different answers here. Three, three, and three. And over here, what do we get? Four, four, and four. How is that? Well, these three here occupy position number three, position number four, position number five, in no particular order. In other words, they share the third, fourth, and fifth position. Therefore the average of those is four.

And that's why we're getting this number. Now one of the bit of, and again, it might be trivia that be helpful, if I click column F, in the status bar at the bottom of the screen, I do get a total of all these numbers. It's 29,642. If I click in column G, it's 29,646. So obviously that's different. Just not sure what role it plays. Now with a much smaller list, perhaps this has different meaning. I've got information off to the right. I've got the same kind of formulas. I'll just take these two formulas here and drag this with the left mouse button and the control key.

I'll copy them into the list over here to the right and copy this. I could have done those together. And for the moment here, we can see what's going on. There are no duplicates at the moment, but to create a duplicate, I'll make these two be the same. I'll drag this upward. And we see what's happening, same kind of thing. And here it might make more sense in terms of how you might use these. The total here is 77. The total over here is 78. And again, that might pop up differently. So I think most people are going to be using RANK and probably in the order called Default Order here, Descending Order.

If you can find a good use for AVG, and I keep looking for a way to use this sensibly, and I don't think this is incorrect or wrong in any way, but it certainly is a, we get different results here. The main thrust of the whole idea though is there certainly will be times when you have lists you want the information to be ranked based on, for example, a salary column or sales item, something like that. These are the functions to be considering. RANK.EQ, remember, is replacing the RANK function. You can still use both of them in probably for a few more versions, but eventually RANK, the simple RANK, will disappear.