Here we have a table that contains five 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 a “1” for the rank of the first student, “2” for the rank of the second student, and then just double-click the fill handle to copy that down. 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 three arguments: number, ref, and an optional argument called order. “Number” is the number being ranked, “ref” represents an array of 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 the array sorted in descending order. This means the highest number will get a rank of “1”. Test scores are normally ranked from high to low, so in this case we just need to provide an average for number, and the range that contains averages for the reference. This reference 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 the rank is also dynamic. Now let’s look at how to rank race results. As before, “number” is the number we’re ranking, and “reference” is the full set of numbers, D6 to D38, in this case. Like we did in the first example, we need to change the reference to be absolute 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. However, for race results, we need to supply “1” which will calculate rank in ascending order; this means the shortest time will get a rank of “1”. Now if I sort the list with the shortest times on top, you can see that the rank has been correctly calculated.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
