RANK.EQ Function

Returns a number's rank in a list; ties share the top rank.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A whole number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

RANK.EQ tells you where a value sits in a list: 1 for the largest (or, with order 1, for the smallest). Tied values get the same rank, and the next rank is skipped.

It replaces the older RANK function, which works the same way.

Syntax

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

Arguments

ArgumentRequired?What it does
numberYesThe value to rank.
refYesThe list to rank it in. Lock it with $ when filling down.
orderNo0 or left out = largest is 1; 1 = smallest is 1.

Examples

Example 1 — Sales ranking

Ben and Gus tie for 5th, so nobody is 6th.

D2fx=RANK.EQ(C2,$C$2:$C$8)
ABCD
1RepRegionSalesRank
2AnaNorth620002
3BenSouth485005
4CyNorth500004
5DiEast712501
6EdSouth398007
7FayNorth554003
8GusEast485005
=RANK.EQ(C2,$C$2:$C$8) Explain

Example 2 — Fastest is first

For times or costs, rank the smallest as 1.

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

Common mistakes

  • #N/A The number isn't in ref.
  • Forgetting the $ signs. Without $C$2:$C$8, the list shifts as you fill down and ranks come out wrong.

Notes

  • To break ties, add a tiny tiebreaker: =RANK.EQ(C2,$C$2:$C$8)+COUNTIF($C$2:C2,C2)-1 ranks the first of equal values higher.
  • Related functions: RANK.AVG, LARGE, PERCENTRANK.INC, SORT.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.