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.