RANK.AVG

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

Syntax:

RANK.AVG(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.AVG(B1, A1:A5)

returns 4.5

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.AVG(B1, A1:A5, B2)

returns 1.5

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.5
2	15	1	1.5
3	10
4	20
5	15

Application:

Imagine a company wants to rank its employees based on their sales performance for the quarter. They want to give a rank to each employee, but if two or more employees have the exact same sales number, they should receive the same rank, and the next rank in the sequence should be skipped. The RANK.AVG function is perfect for this scenario because it assigns the average rank to ties.

Here is a table of employee sales data:

	Employee	Sales
	A	B
1	Alex	$125,000.00
2	Brenda	$150,000.00
3	Charles	$125,000.00
4	David	$175,000.00
5	Emily	$150,000.00
6	Frank	$110,000.00
7	Grace	$150,000.00

Here is how the RANK.AVG function would be applied to rank the employees based on their sales, from highest to lowest. The formula used would be RANK.AVG([Sales], [Sales Range], 0), where [Sales] is the individual employee's sales figure and [Sales Range] is the entire list of sales figures. The 0 indicates descending order (highest value gets rank 1).

	Employee	Sales	RANK.AVG
	A	B	C
1	Alex	$125,000.00	5.5
2	Brenda	$150,000.00	3
3	Charles	$125,000.00	5.5
4	David	$175,000.00	1
5	Emily	$150,000.00	3
6	Frank	$110,000.00	7
7	Grace	$150,000.00	3

Explanation of the RANK.AVG results:

David has the highest sales at $175,000, so he gets the rank of 1.
Brenda, Emily, and Grace are all tied for the next three ranks (2, 3, and 4) with sales of $150,000. RANK.AVG calculates the average of these ranks: (2+3+4)/3=3. Therefore, they all receive a rank of 3. The next available rank is 5.
Alex and Charles are tied for the next two ranks (5 and 6) with sales of $125,000. RANK.AVG calculates the average of these ranks: (5+6)/2=5.5. They both receive a rank of 5.5. The next available rank is 7.
Frank has the lowest sales at $110,000, so he receives the next available rank, which is 7.