RANKAVG

Returns the position of a number in a list. If there are duplicates, this function gives them the average rank.

Returns the rank/position of a number in a list of numbers.

A number's rank refers to its size or value in comparison to the values of other numbers in a list. If you were to sort the list (ascending or descending), the rank of the number would be its position.

If more than one value has the same rank, the average rank is returned.

Syntax

RANKAVG(number, list, [order])

Arguments

The RANKAVG function syntax has the following arguments:

• number: The number whose rank you wish to find. Required.

• list: A list of numbers, or a reference to it. Required.

• order: A number that specifies whether the numbers should be ranked ascending or descending in the list. Optional.

If the order is 0 (zero), Inforiver ranks the number as if the list is sorted in descending order. The highest value number is assigned a rank of 1, and so on.

If the order is omitted or any nonzero value is entered, Inforiver ranks the number as if the list is sorted in ascending order. The lowest value number is assigned a rank of 1, and so on.

Example

RANKAVG(2,[1,2,2,5,4],0)
// returns 3.5

RANKAVG(2,[1,2,2,5,4],1)
// returns 2.5

The RANKAVG function can be used to rank the values of multiple entities in important metrics such as score, cost, revenue, and sales, particularly when there is a possibility of more duplicate values.

In the example below, we have used the RANKAVG function to rank the sales across different product categories. The average rank is returned for duplicates.

RANKAVG function

We used the SELECT function to select a range of columns from 1 to 5 for the 'Sales' measure.

Excel Equivalent

RANKAVG