RANK.AVG function

The RANK.AVG function in Excel is used to determine the rank of a number in a list of numbers, returning the average rank for duplicate values. This is useful for ranking data in statistical analysis where ties occur, as it calculates the average rank for identical numbers.

Syntax

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

Parameters

1. number (required): The number you want to rank.
2. ref (required): The array or range of numbers in which you want to find the rank.
3. order (optional):
   • 0 or omitted: Ranks in descending order (largest to smallest).
   • 1: Ranks in ascending order (smallest to largest).

Key Points

• Duplicate Values: When duplicate values exist, the RANK.AVG function assigns each duplicate the average of their ranks.
• Order:
  • Use 0 (descending) to rank numbers where the largest number is ranked 1.
  • Use 1 (ascending) to rank numbers where the smallest number is ranked 1.
• Dynamic Data: If the data in ref changes, the rank will automatically update.

Examples

1. Basic Ranking (Descending Order)

Suppose you have the following dataset in A1:A5:
{10, 20, 20, 40, 50}.

To rank the value 20:

=RANK.AVG(20, A1:A5, 0)

Result: 3.5

• The two 20s share the 3rd and 4th ranks, so the average rank is $(3 + 4) / 2 = 3.5$.

2. Basic Ranking (Ascending Order)

Using the same dataset {10, 20, 20, 40, 50}:

To rank the value 20 in ascending order:

=RANK.AVG(20, A1:A5, 1)

Result: 2.5

• The two 20s share the 2nd and 3rd ranks, so the average rank is $(2 + 3) / 2 = 2.5$.

3. Ranking Unique Values

In the dataset {5, 10, 15, 20, 25}, ranking 15 in descending order:

=RANK.AVG(15, A1:A5, 0)

Result: 3

• Since all values are unique, the rank corresponds to the position in descending order.

4. Dynamic Range

If you reference a range that changes (e.g., {10, 30, 30, 50} becomes {10, 20, 30, 50}), the rank of numbers will automatically update based on the new data.

Notes

• Use with Conditional Formatting: You can combine RANK.AVG with conditional formatting to highlight specific ranks in a dataset.
• Handling Errors:
  • If number is not found in ref, Excel returns a #N/A error.
  • Ensure that ref contains at least one numeric value, or Excel will return a #VALUE! error.

Related Functions

• RANK.EQ: Returns the rank of a number without averaging for duplicates (uses the first rank for ties).
• LARGE: Returns the k-th largest value in a dataset.
• SMALL: Returns the k-th smallest value in a dataset.
• PERCENTRANK.INC: Calculates the rank of a number as a percentage of the dataset (inclusive).
• PERCENTRANK.EXC: Calculates the rank of a number as a percentage of the dataset (exclusive).

The RANK.AVG function is particularly useful in statistical analysis and reporting, as it provides a fair way to rank numbers when ties exist, ensuring that no single duplicate is favored over another.