In this video, we’ll look at how to rank values in ascending or descending order using the RANK function.
Here we have a table that contains 5 test scores for a group of students and an average score in Column I.
How can we rank these students from highest to lowest scores?
Well, one option is to sort the students by average score in descending order. Next, you can enter 1 for the rank of the first student in the list, 2 for the second, in then just drag down with the fill handle to fill in the rest of the ranks.
This works fine, but it’s not dynamic. If the test scores will be changing over time, or if you don’t want to sort the list first, a better approach is to use the RANK function to calculate a rank.
Let me undo these last changes and let’s try it out.
The RANK function takes 3 arguments: number, reference, and and optional argument called order. Number is the number being ranked, ref represents the numbers to rank against, and order specifies if rank should be calculated in ascending or descending order.
If order is 0 or omitted, number is ranked by position against the numbers in array sorted in descending order. The highest number will get a rank of 1.
Test scores are normally ranked high to low, so in this case we just need to provide the average for number, and the range that contains averages for ref. Ref needs to be absolute so that it won’t change as the formula is copied down.
Now each student has a rank. If I temporarily sort the list by average, you can see that the ranking is correct. And if I manually adjust the test scores, you can see that rank is also dynamic.
Now let’s look at a ranking of race results.
As before, number is the number we are ranking and array is the full set of numbers, D6 to D38 in this case.
Again we need to change the reference to absolute format so that it won’t change when copied.
This time we need to specify order. The default is zero which calculates a rank in descending order.
For race results, we need to provide 1, which will calculate rank in ascending order – the shortest time will get a rank of 1.
If I sort the list from shortest to longest times, you can see that rank has been correctly calculated.