RANK.AVG function

RANK.AVG(value, array, order?) RANK.AVG(value; array; order?)

value — Number or { Number }

The value whose rank in the given array should be returned.

array — { Number }

The array of values in which the given value is expected to appear.

order — SortOrder, Number, { SortOrder } or { Number } (optional)

The sort order, SortOrder.DescendingSortOrder,Descending or 0 for descending order (greater values appear first in the sorted array used to determine the rank) and SortOrder.AscendingSortOrder,Ascending or any number other than 0 for ascending order (greater values appear last in the sorted array). If omitted, descending order is used.

Returns — Number or { Number }

The rank of the given number in the given number array.

Returns the rank of the given number in the given number array. RANK.AVG(10, { 10, 1, 100, 1000 })RANK.AVG(10; { 10; 1; 100; 1000 }) returns 3, because 10 is at the third position in a version of the given array that has been sorted in descending order.

Consider this formula, with an array containing two 10 elements:

RANK.AVG(10, { 10, 1, 100, 1000, 10 })RANK.AVG(10; { 10; 1; 100; 1000; 10 })

Sorting { 10, 1, 100, 1000, 10 }{ 10; 1; 100; 1000; 10 } in descending order yields the array { 1000, 100, 10, 10, 1 }{ 1000; 100; 10; 10; 1 }, where 10 appears at positions 3 and 4. RANK.AVG returns the average of these two positions, 3.5. RANK.EQ would instead return the lower rank, 3.

The third, optional parameter may be given to specify the sort order. Use SortOrder.DescendingSortOrder,Descending or 0 for descending order (greater values appear first in the sorted array) and SortOrder.AscendingSortOrder,Ascending or any number other than 0 for ascending order (greater values appear last in the sorted array). This function accepts numbers in addition to descriptive names for the sort order to ensure compatibility with spreadsheets.

Examples

RANK.AVG(10, { 10, 1, 100, 1000 })RANK.AVG(10; { 10; 1; 100; 1000 })

Returns 3, because 10 is at the third position in a sorted version of the given array, sorted in descending order.

RANK.AVG(10, { 10, 1, 100, 1000, 10 })RANK.AVG(10; { 10; 1; 100; 1000; 10 })

Returns 3.5. 10 appears twice in the given array, at positions 3 and 4 in a sorted version of the array, sorted in descending order. RANK.AVG returns the average of these two positions, 3.5. RANK.EQ would instead return the lower rank, 3.

RANK.AVG(10, { 10, 1, 100, 1000 }, SortOrder.Descending)RANK.AVG(10; { 10; 1; 100; 1000 }; SortOrder,Descending)

Returns 3, because 10 is at the third position in a sorted version of the given array, sorted in descending order. The sort order can be specified explicitly.

RANK.AVG(10, { 10, 1, 100, 1000 }, SortOrder.Ascending)RANK.AVG(10; { 10; 1; 100; 1000 }; SortOrder,Ascending)

Returns 2, because 10 is at the second position in a sorted version of the given array, sorted in ascending order.

RANK.AVG(10, { 10, 1, 100, 1000 }, 0)RANK.AVG(10; { 10; 1; 100; 1000 }; 0)

Returns 3, because 10 is at the third position in a sorted version of the given array, sorted in descending order. The sort order can be specified explicitly. This function accepts a numeric sort order to ensure compatibility with spreadsheets.

RANK.AVG(10, { 10, 1, 100, 1000 }, -1)RANK.AVG(10; { 10; 1; 100; 1000 }; -1)

Returns 2, because 10 is at the second position in a sorted version of the given array, sorted in ascending order. The sort order can be specified explicitly. This function accepts a numeric sort order to ensure compatibility with spreadsheets.