Note
In Excel 2010, the RANK() function was replaced with the RANK.EQ() and RANK.AVG() functions to increase the accuracy of the results. To ensure the backward compatibility of RANK.EQ() and RANK.AVG(), the RANK() function is still available.
Syntax. RANK(number,ref,order)
Definition. This function returns the rank of a number within a list of numbers.
The rank of a number is its size relative to other values in a list. If you were to sort the list, the rank of the number would be its position.
Arguments
number (required). The value for which you want to know the rank.
ref (required). An array of or a reference to a list of numbers. Nonnumeric values in ref are ignored.
order (optional). A number specifying how to rank numbers. If order is 0 or not specified, the number is ranked as if ref were a list sorted in descending order. If order is a nonzero value, the number is ranked as if ref were a list sorted in ascending order.
Background. This function is especially useful if you work with large amounts of data, because manually ranking values is difficult and time-consuming.
Note that RANK() gives duplicate numbers the same rank and skips the subsequent rank. For some purposes, you might want to use a definition of rank that takes identical values into account. This can be done by adding the following correction factor to the value returned by RANK(). This correction factor is appropriate when the rank is calculated in descending order (order = 0 or omitted) or ascending order (order = nonzero value).
The correction factor for tied ranks is:
[COUNT(reference) + 1 – RANK(number, reference, 0) – RANK(number, reference, 1)]/2
Example. The software company has created a table with the sales of the past two years. Because the manager wants to know the ranks of the months, you have to rank the sales (see Figure 12-120).
The Rank column contains the ranks 1 through 24 for the sales of the corresponding month. Because all sales were different, all ranks are used.