RANK.EQ function

The RANK.EQ function in Excel is used to determine the rank of a number in a list of numbers. Unlike RANK.AVG, if there are duplicate values, they are assigned the same rank, and the next rank is skipped. This function is useful for ranking data when ties can have the same rank without averaging.

Syntax

=RANK.EQ(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: Duplicate values are assigned the same rank. The next rank is skipped.
• Order:
  • Descending (0 or omitted): Higher numbers have a higher rank (rank 1 is the largest value).
  • Ascending (1): Lower numbers have a higher rank (rank 1 is the smallest value).
• Dynamic Data: If the data in ref changes, the rank will automatically update.

Examples

1. Basic Ranking (Descending Order)

Suppose the dataset is {10, 20, 20, 40, 50} in A1:A5.

To rank the value 20:

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

Result: 3

• Both 20s are ranked 3, and the next rank after them is 5.

2. Basic Ranking (Ascending Order)

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

To rank the value 20 in ascending order:

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

Result: 2

• Both 20s are ranked 2, and the next rank after them is 4.

3. Ranking Unique Values

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

=RANK.EQ(15, B1:B5, 0)

Result: 3

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

4. Dynamic Range

If the dataset changes (e.g., from {10, 30, 30, 50} to {10, 20, 30, 50}), the rank of numbers will automatically update to reflect the new values.

Notes

• Use with Conditional Formatting: RANK.EQ can be used with conditional formatting to highlight specific ranks or top/bottom values.
• 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.
• Skipping Ranks: After ties, the function skips ranks, meaning if two numbers are tied at rank 3, the next rank will be 5.

Related Functions

• RANK.AVG: Similar to RANK.EQ but calculates the average rank for duplicate values.
• 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.EQ function is ideal for ranking data when duplicate values should share the same rank without averaging, and it’s commonly used in data analysis, competitions, and scoring systems.