RANK, RANK.EQ and RANK.AVG functions for ranking data in Excel

For the ranking of data in Excel, the statistical functions RANK, RANK.EQ, RANK.AVG are used. All of them return the number of the numeric in the ranked list of the numerical values. Let's consider more at the syntax, examples.



The example of RANK function in Excel

The function is used for ranking in the list of numbers - that is, you can find out the value of a number relative to other numerical values. If you sort to the list in ascending order, the function will return to the position of the number. For example, in the array of numbers {30; 2; 26}, the number 2 will have to the rank 1; 26 - 2; 30 - 3 (as the largest value in the list).

The syntax of the function:

  1. The numeric, for which it is necessary to determine the number in the ranking.
  2. There is reference on an array of numbers or the range of cells with numeric values. If you specify as an argument to the simple numbers, then the function returns an error. For non-numeric values to a number is not assigned.
  3. The order is the ordering of the numbers in the list. There are variants: the argument is «0» or omitted - the value 1 is assigned to the maximum number in the list (as if the list is sorted in descending order); the argument is equal to any unequal zero number - the ranking number 1 is assigned to the minimum number in the list (as if the list is sorted in ascending order).

Let's determine to the ranking of the numbers in the list without repeating:

without repeating.

The argument that determines to the way of ordering numbers is «0» - therefore, in this function, the numbers are assigned to values from higher to lower. The maximum number of 87 is numbered 1.

argument that determines.

The third column shows to the formula with the rank in ascending order.

Let`s define to the numbers of values in the list, where there are duplicate values.

duplicate values.

The repeating numbers are highlighted by yellow color and the same number is determined for ones. For example, the number 7 in the second column is assigned to the number 9 (both in the second line, and in the ninth one); in the third column – is 3. But none of the numbers in the second column will have 10, and in the third – is 4.

So that the ranks do not recur (sometimes this prevents by the user from solving the task), the following formula is used:

ranks do not recur.

You can set limits for the function: for example, you need to rank the values from 0 to 30 only. To solve the problem, we apply to the function:

for example.

The values that correspond to the specified condition are highlighted in gray. For numbers that are greater than 30, an empty string is printed.



There is example of the function RANK.EQ in Excel

In the Excel versions, starting from 2010, the function RANK.EQ was appeared. This is an absolute analogue of the previous function. The syntax is the same. The letters «RV» in the title indicate that if a formula is found with the same values, the function will return the highest ranking number (that is, the first detected element in the list of equal).

RANK.EQ.

As you can see from the example, this function handles to the duplicate numbers in the list in exactly the same way as in the usual formula. If it is necessary to avoid repetitions of ranks, we use to another formula (see above).

There is the example of the RANK.AVG function in Excel

It returns to the numbers of a numeric value in the list (the sequence number relative to other values) - that is, it performs the same task. Only when identical values are found, it returns the average.

Here is the result of the function:

result of the function.

There is the formula in the column «in descending order»: = RANK.AVG. So, the function assigned 87 to the average number of 1.5.

Supposedly, there are three repeated values in the list of numbers (highlighted in orange).

RANK.AVG.

The function assigned to each of them rank 5, which is the average for 4, 5 and 6.

Let us compare to the work of the two functions:

Let us compare.

Download examples RANK, RANK.AVG and RANK.EQ functions in Excel.

Recall that these two functions work in Excel 2010 and higher only. In earlier versions, you can use the array formula for these purposes.

Thus, all of the above examples allow you to automate the work of ranking data and ranking values without the use of sorting.


en ru