The RANK function gives you a way to rank values with a simple function that will dynamically calculate rank. If any value changes, you'll get a new rank automatically.
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.
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can determine the rank of largest (i.e. top sales) values as well as smallest (fastest time) values, which is controlled with the...
The Excel AVERAGE function returns the average of values supplied as multiple arguments. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
I work as a financial analyst and your page is a leading one I use to find so important info for daily tasks like formula explanation, how to construct different formulas and so on. This is brilliant! You helped me a lot. - Sarunas