Ranks
One of the first things you learned in Excel were the functions for determining the maximum (MAX) and minimum (MIN) of a range. Also, sorting a range can easily determine the order of its members, from the smallest to the largest or in the opposite direction. But, since it’s not convenient to manually count the members’ ranking, we can use one of the Excel ranking functions.
SMALL and LARGE functions are used to determine the lowest and highest ranks in the array. The first one has a syntax:
SMALL (<range>, <rang>)
For example, if in a given set of numbers we want to find the second smallest member we will write the formula:
=SMALL($A$2:$A$11,2)
Similarly, to determine the rank of the largest members, the LARGE function is used, whose syntax is:
LARGE (<range>, <rang>)
If we want to determine the second largest member of a range we will write the formula:
=LARGE($A$2:$A$11,2)
As you can see, the first argument of these functions is always a range, and the second is the rank-order relative to its maximum or minimum.
We can also use the RANK function, which has the syntax:
RANK(<number>,<range>,<order>)
The first argument of a function is the value which we want to rank, the second argument is a range of data, and the last is 1 (ascending order) or 0 (descending order). For example, if we write the formula:
=RANK(5,$A$2:$A$11,0)
we get a value of 8, which means that number 5 is the eighth member in the ranking from below. If we wrote the formula:
=RANK(5,$A$2:$A$11,1)
The result would be 3, because number 5 is the third member of a range ranking from above.
In new versions of Excel, instead of the RANK function, we can also use RANK.EQ, which are practically the same functions with different names. There is another function with the same syntax called RANK.AVG. We use it when we have repeating members of a range. For example, if we write a formula:
=RANK.AVG(8,$A$2:$A$11,1)
The result is 4.5. This means that number 8 appears twice, at the 4th and 5th positions in a range.