Calculating Rank In Mac Numbers

If you need to find the ranking of rows in Numbers, you can sort, use COUNTIF or use RANK. It gets a little more complex if you have to use multiple values for tie-breakers or rank within groups.
You can also watch this video at YouTube (but with ads).
Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how to use Rank in Mac Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So sometimes in Numbers you want to rank your rows in a table. There are several different ways to do this. For instance, let's say you have a table like this with just a list of grades for students. Let's say you want to see who is first, second, third, and so on. Well, you can do that really easily without any formula by just sorting. I'm going to click here on B at the top and say Sort, Descending. The will put the largest at the top. You could see here that Mary has got 96, Jessica 91, etc. So you can see who is first, second, and third. If you want to have the ranking number you could do that by simply using Row. So I'm going to start a new formula with the equals key and type ROW and then Return and just return again and it will give me the row for the current cell which is 2 because 1 is the Header. So what I want to do here is subtract 1 and I'm going to Copy, double-click on the Header here for C, so I select all of the non-header rows in C. Paste. Now I can see 1, 2, 3, 4, 5, and so on. 
Except this doesn't work very well because notice there's a tie for 5th. There are three students with an 86. They are shown as 5th, 6th, and 7th. To do it properly they should all be ranked 5th. Then this student here would be ranked 8th. So while Sorting gives you a quick way to do it, it's not going to be perfect. 
So, instead let's try to use a formula. We can use COUNTIF for this. What Count If does is it will count the number of things that meet a certain criteria. So I"ll do equals, COUNTIF, parenthesis, and if I search over here for it and look at the Help you could see it takes the array and a condition. So in this case the array would be everything in B. So I could just click on B here and it puts the name of that column, Score.  Then, what is the Condition. Well, I want to look for scores that are greater than that student's score. So in quotes I''ll put greater than like that">". Then I'll use ampersand to append this value here. So in this case it would be scores that are greater than 79 and I'll close the parentheses. You can see there are 13 scores that are better than 79. I copy and then paste that throughout the entire column you could see that here the top score has a zero because there are no scores better than 96. So ranking is going to start with 0. I could adjust that by simply adding 1. I'll copy that and paste that throughout. Now I can see that the top score is 1, the next best is 2. Let's sort these and we can see that these three students are all given 5 because there are only four scores, plus one, five better than the 86. So it properly shows them as all ranked as 5th and then the next student ranked as 8th. 
Now it's important to know how to do that using COUNTIF because we're going to use that later. For now, let's go and look at another function that's called simply RANK. This does exactly what we want. So I'll do RANK and rank kind of asks for things in the opposite order. We want the rank for this value in all of B. Now we get to say 14. If we Copy and then Paste throughout you could see I get the ranking there pretty simply. Let's sort it and we can see I get 1, 2, 3, 4, 5. Five is repeated three times. Then 8 after that. So this is the easiest way to do it. 
What if you want to do a ranking that takes into account two things. So here I've got the score, which should be the primary thing for ranking. But for tie-breakers there's an extra thing here. Maybe a score in a quiz or behavior or something like that. So we want to take both of these into account. One way to handle that would be to add these two together in someway that makes sense. So, for instance, we can do the major score here and let's multiply that by a like a 100. Plus, the minor score here. So now you can see 7912. If I Copy and Paste these all in you could see it puts them like that and now I can do a ranking based on this number here, like that, in this whole column. Now when I Paste it in you could see I get tie-breakers. So, sorting would be like this. You could see there's no tie for 5th place anymore because all of these, while they are 86's here, they've got different secondary values and that is taken into account. 
Now let's say you want to get ranking but also by group. So here I've divided all the students up into two classes, a west and east class, and I want to see who is first in west and who is first in east, etc. So the RANK function isn't going to help us here. But we can go back to the COUNTIF function. In fact we're going to use COUNTIFS, with a S at the end there. That gives us this here where we can actually have multiple test values and multiple conditions. So the first set of test values are going to be just like before. We're going to test the score and we're going to see what is greater than > the score for that student. The second test condition is going to test the class here and it's going to see if it's equal to this particular class that the student is in. We don't need to put an equals ampersand like that. If it is just equals we can just have the value there. 
So it is going to look at two things. It's going to look at how many scores are greater than the score for this one student. But also only in rows where the class name is the same as the class name for that student. This is going to give us a ranking that starts with zero, like before, So we want to do a +1 here. So I get a 5 there and let me Copy and Paste throughout here and I get rankings. Notice there's a 1 and there's a 1, two different scores, but Mary here is in east and has a 96. Jessica is in west and has a 91.  So now that we have those rankings we can sort and we can actually sort twice. We can either use Organize Sort and add two sort criteria right here or we can simply Sort first Ascending by Rank and then Sort Ascending by class. It puts all of east together and we get 1st, 2nd, and 3rd for east. It groups all of west together and we get 1st, 2nd, 3rd for west and it still handles ties properly. So there are two scores of 79 for west and there are both ranked 5th. This will work for any number of groups. So you can have four classes or a hundred classes and it would still rank everybody properly. 
So if you have to figure out some sort of ranking in Numbers there's are several ways that you can handle it. I hope you found this useful. Thanks for watching.
Calculating Rank In Mac Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
