Buzz
This article introduces you to the RANK.EQ function, one of the statistical functions widely used in Excel.
Description: The function returns the rank of a number in a list of numbers based on the size of the number in relation to other values. If multiple values share the same rank -> the function returns the highest rank. This function is supported from Excel 2010 onwards.
Syntax: RANK.EQ(number, ref, [order])
In which:
- number: The value you want to find the rank of, a required parameter.
- ref: An array or reference to a list of numbers, a required parameter.
- order: A number indicating how to rank the values, an optional parameter.
Note:
- If order = 0 or omitted -> the function defaults to ranking values in descending order.
- If order ≠ 0 -> the function defaults to ranking values in ascending order.
Example:
Determine the rank of the value 66 in the data array described in the table below:
- Determine the rank of the value 66 in descending order. In the cell where the calculation is needed, enter the formula: =RANK.EQ(D6,D7:J7,D8)
- Press Enter -> the rank of the value 66 is:
- Here, the ref array has 3 values of 66 -> the function returns the highest rank, which is 1 (corresponding to descending order).
- Determine the rank of the value 66 in ascending order. In the cell where the calculation is needed, enter the formula: =RANK.EQ(D6,D7:J7,D8) -> Press Enter -> the returned value is:
- Here, the ref array has 3 values of 66 -> the function returns the highest rank, which is 5 (corresponding to ascending order).
So, with two different sorting methods, the rank of the same value is different.
Here is a guide and some specific examples when using the RANK.EQ function in Excel.
Wishing you all success!
