RANK.EQ & RANK.AVG Ranking Formulas

RANK.EQ & RANK.AVG Ranking Formulas

When presenting sales leaderboards, student exam results, or warehouse delivery speed comparisons, you need to assign ordinal ranks (1st place, 2nd place, 3rd place). But a tricky problem arises whenever two scores tie.

Consider two sales representatives who both closed exactly $85,000 in sales, tying for 2nd place. How should Excel treat the next representative? In standard competitive sports ranking, both reps receive Rank 2, and the next person drops to Rank 4 (skipping Rank 3). In statistical tie-splitting, both receive an average rank of 2.5.

Excel provides two dedicated functions to handle this: RANK.EQ (standard sports ranking) and RANK.AVG (fractional average ranking).

Quick Comparison: RANK.EQ vs RANK.AVG on Tied Scores

How the Ranking Engines Work

1. Syntax and the Order Argument

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

• number: The value whose rank you want to find.
• ref: The reference list of numbers (always lock with dollar signs like $C$2:$C$6).
• order (optional):
  • 0 (or omitted): Descending (Highest number gets Rank 1). Ideal for sales and test scores.
  • 1: Ascending (Lowest number gets Rank 1). Essential for race lap times, golf scores, defect counts, and delivery durations.

2. The Absolute Reference Locking Rule

When dragging ranking formulas down a column, you must lock the comparison list range with dollar signs ($C$2:$C$6). If you leave it relative (C2:C6), the evaluation range shifts with every row, producing nonsensical ranks!

Real-Life Workplace Scenarios

1. Sales Performance Commission Tiers

Sales ops teams assign quarterly prize bonuses to the Top 3 performers using RANK.EQ.

2. Delivery Route Efficiency (Ascending Rank)

Logistics supervisors rank truck route durations from shortest to longest using order = 1.

Common Mistakes to Avoid

• Forgetting Dollar Signs on the Reference Range: Forgetting $ is the most frequent ranking error.
• Using the Deprecated RANK Formula: The old RANK formula is maintained only for legacy files. Always use RANK.EQ or RANK.AVG in new sheets.

Audit rankings and handle ties in our spreadsheet playground below.