How to Use the RANK.EQ and RANK.AVG Functions in Microsoft Excel to Rank Datasets with Ties

Sorting and ranking datasets is a fundamental requirement in data analysis, performance scoring, and financial modelling. While Microsoft Excel originally offered the legacy RANK function, modern Excel introduced two specialized replacements: RANK.EQ and RANK.AVG. These updated functions provide explicit control over how duplicate values and tied rankings are resolved across numerical distributions, ensuring statistical rigor and consistency across your workbooks.

Understanding the Difference Between RANK.EQ and RANK.AVG

When ranking unique numbers, both functions yield identical results. However, their calculation behaviour diverges fundamentally when duplicate values occur:

• RANK.EQ (Rank Equal): Assigns duplicate values the highest (top) rank of that group. If two values tie for 2nd place, both receive rank 2. The next subsequent value skips rank 3 and receives rank 4. This mirrors standard Olympic medal or tournament standings.
• RANK.AVG (Rank Average): Calculates and returns the arithmetic mean of the tied ranks. If two items tie for 2nd and 3rd place, both receive an average rank of 2.5 ((2 + 3) / 2). If three items tie across positions 4, 5, and 6, each receives rank 5 ((4 + 5 + 6) / 3). This method is preferred in non-parametric statistical analysis and econometrics.

Syntax and Parameter Reference

Both functions share an identical argument structure:

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

Argument	Required / Optional	Description
number	Required	The individual numerical value whose rank position you want to identify.
ref	Required	A reference to an array or range of numbers. Non-numeric values in ref are automatically ignored.
order	Optional	A flag specifying ranking direction. 0 or omitted ranks descending (highest number is 1). Any non-zero value (such as 1) ranks ascending (lowest number is 1).

How to Use RANK.EQ for Tournament or Sales Rankings

Follow these steps to rank a list of sales representatives or student exam scores using standard competition ranking:

1. Open your worksheet with values arranged in a column (e.g., scores in range B2:B10).
2. Click on the cell where you want your first rank result to appear (e.g., cell C2).
3. Enter the following formula, locking the reference array with absolute cell references:

   =RANK.EQ(B2, $B$2:$B$10, 0)

4. Press Enter.
5. Select cell C2 and drag the fill handle down to cell C10 to copy the formula across the entire range.

Using $B$2:$B$10 ensures that the comparison array stays fixed while the target cell relative reference B2 dynamically adjusts for each row.

How to Use RANK.AVG for Statistical Data Analysis

When conducting statistical evaluations where tied observations must preserve the total rank sum of the sequence, follow these instructions:

1. Select an empty column adjacent to your data (e.g., cell D2).
2. Type the formula:

   =RANK.AVG(B2, $B$2:$B$10, 0)

3. Press Enter and populate the remaining rows down to D10.
4. Any instances of duplicate scores will now display fractional average positions (such as 3.5, 6.0, or 7.5), preserving accurate variance and mean distributions across downstream calculations.

Comparison of RANK.EQ vs RANK.AVG on a Sample Dataset

Consider a practical performance test where two candidates tied with a score of 88, and two tied with a score of 74:

Candidate	Score	RANK.EQ Formula	RANK.AVG Formula	Explanation
Alice	95	1	1	Highest score in dataset.
Bob	88	2	2.5	Tied with Clara. EQ gives 2; AVG averages positions 2 and 3.
Clara	88	2	2.5	Tied with Bob. EQ gives 2; AVG averages positions 2 and 3.
David	81	4	4	Position 3 was consumed by the preceding tie; next rank is 4.
Emma	74	5	5.5	Tied with Frank. EQ gives 5; AVG averages positions 5 and 6.
Frank	74	5	5.5	Tied with Emma. EQ gives 5; AVG averages positions 5 and 6.
Grace	62	7	7	Both methods resume consistently at rank 7.

Ranking in Ascending Order for Race Times or Golf Scores

In scenarios such as race lap timings, golfing scores, or cost metrics, the lowest numerical value represents the optimal performance. To achieve ascending ranking, supply 1 as the third parameter:

=RANK.EQ(B2, $B$2:$B$10, 1)

In this configuration, the smallest number in the designated reference range receives rank 1, with higher values receiving progressively larger rank numbers.