RANK.AVG

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

Returns the rank of a value in a list, table or cell range (in descending order) (average ranking).

number	The number whose rank you want to find.
ref	An array or list of numbers.
order	(Optional) The number indicating how the list of numbers is sorted: 0 = Descending order, Largest number has a rank of 1 (default) 1 = Ascending order, Smallest number has a rank of 1

REMARKS

* For an illustrated example refer to the page under Advanced Functions
* The "ref" does not have to be sorted into any particular order.
* If "ref" contains any non numeric values, then these are ignored. See Example 12.
* If "order" = "0", then the smallest numbers are given the largest rank.
* If "order" = "1", then the largest numbers are given the largest rank.
* If "order" is left blank, then 0 is used.
* Any duplicate numbers are given the average ranking. See Example 4.
* You can use the RANK.EQ function to return equal ranking when more than one value has the same rank.
* This function was added in Excel 2010
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C
1	=RANK.AVG(10,B1:B6) = 6	10	60
2	=RANK.AVG(10,B1:B6,0) = 6	20	10
3	=RANK.AVG(10,B1:B6,1) = 1	30	30
4	=RANK.AVG(20,C1:C8) = 6.5	30	20
5	=RANK.AVG(20,C1:C8,0) = 6.5	40	50
6	=RANK.AVG(20,C1:C8,1) = 2.5	50	30
7	=RANK.AVG(30,B1:B6,0) = 3.5	some text	20
8	=RANK.AVG(20,B1:B6,0) = 5	60	30
9	=RANK.AVG(30,B1:B6,1) = 3.5
10	=RANK.AVG(20,B1:B6,1) = 2
11	=RANK.AVG(20,B1:B8,0) = 6
12	=RANK.AVG(40,B1:B2,1) = #N/A

1 - What is the rank of the number 10 in the list "B1:B6". By default the smallest numbers are ranked at the bottom.
2 - What is the rank of the number 10 in the list "B1:B6" when the largest number has a rank of 1.
3 - What is the rank of the number 10 in the list "B1:B6" when the smallest number has a rank of 1.
4 - What is the rank of the number 20 in the list "C1:C8". There are two 20's taking positions 6 and 7.
5 - What is the rank of the number 20 in the list "C1:C8" when the largest number has a rank of 1. There are two 20's taking positions 6 and 7.
6 - What is the rank of the number 20 in the list "C1:C8" when the smallest number has a rank of 1. There are two 20's taking positions 2 and 3.
7 - What is the rank of the number 30 in the list "B1:B6" {50,40,30,30,20,10} when the largest number has a rank of 1. There are two 30's taking positions 3 and 4.
8 - What is the rank of the number 20 in the list "B1:B6" {50,40,30,30,20,10} when the largest number has a rank of 1.
9 - What is the rank of the number 30 in the list "B1:B6" {10,20,30,30,40,50} when the smallest number has a rank of 1. There are two 30's taking positions 3 and 4.
10 - What is the rank of the number 20 in the list "B1:B6" {10,20,30,30,40,50} when the smallest number has a rank of 1.
11 - What is the rank of the number 30 in the list "B1:B8" {60,"some text",50,40,30,30,20,10} when the largest number has a rank of 1.
12 - What is the rank of the number 40 in the list "B1:B2" {10,20}. This number is not in the list.