RANK.EQ

Determines the rank of a number within a list of numbers. This function will return the top rank in the list of numbers if more than one number has the same rank.

Syntax:

RANK.EQ(number, ref, [order])

number is required, and is the value that you want to use to determine the rank.

ref is required, and is the array or range of cells containing the list of numbers.

order is optional, and specifies the order of ranking.

0 or omitted: Ranks the numbers in descending order (highest value gets rank 1).

Any other value: Ranks the numbers in ascending order (lowest value gets rank 1).

Example:

If number, found in B1, contains 10 and ref, found in A1:A5, contains numbers 10, 15, 10, 20, 15:

RANK.EQ(B1, A1:A5)

returns 4

If number, found in B1, contains 10, ref, found in A1:A5, contains numbers 10, 15, 10, 20, 15 and order, found in B2, contains 1:

RANK.EQ(B1, A1:A5, B2)

returns 1

Result for example not using order is found in C1.

Result for example using order is found in C2.

	A	B	C
1	10	10	4
2	15	1	1
3	10
4	20
5	15

Application:

Scenario: A company wants to rank its sales representatives based on their total sales for the last quarter. The representative with the highest sales will receive a bonus.

Here is the table of sales data:

	Representative	Total Sales
	A	B
1	Alex	$150,000.00
2	Brenda	$185,000.00
3	Charles	$120,000.00
4	David	$185,000.00
5	Emily	$210,000.00
6	Frank	$150,000.00

To determine the rank of each representative, you would use the RANK.EQ function in a new column. The formula for the first representative, Alex, would be:

RANK.EQ(B1, B1:B6, 0)

Let's break down the formula:

B1: This is the number you want to rank, which is Alex's total sales.
B1:B6: This is the ref, or the range of all the numbers in the list (the total sales column). The dollar signs $ are used to create an absolute reference so that when you copy the formula down, the range B1:B6 does not change.
0: This is the [order]. A 0 (or omitted) indicates descending order, meaning the highest sales number will receive the rank of 1. A 1 would indicate ascending order.

After applying this formula to all representatives, the table with the ranks would look like this:

	Representative	Total Sales	Rank
	A	B	C
1	Alex	$150,000.00	4
2	Brenda	$185,000.00	2
3	Charles	$120,000.00	6
4	David	$185,000.00	2
5	Emily	$210,000.00	1
6	Frank	$150,000.00	4

Analysis of the results:

Emily has the highest sales ($210,000) and is ranked #1.
Brenda and David both have sales of $185,000, so they share the rank of #2. The next rank assigned is #4, skipping #3. This is a key feature of the RANK.EQ function: it assigns the same rank to duplicate values and the next rank is skipped.
Alex and Frank both have sales of $150,000, so they share the rank of #4. The next rank assigned is #6, skipping #5.
Charles has the lowest sales and is ranked #6.

Using RANK.EQ, the company can quickly identify the top performer (Emily) to award the bonus and also see how other representatives compare to each other.