RANK.EQ function
Quick answer
RANK.EQ tells you where a number places within a list: 1 for the largest by default, or 1 for the smallest if you set the order to 1.
- Syntax
=RANK.EQ(number, ref, [order])- Most common use
=RANK.EQ(B2,B2:B7)returns 2 (Ana is second; Lee has the top sales)
Tied values share a rank and the next rank is skipped, the same way a leaderboard handles ties. The older RANK function works the same way.
- Available in
- Excel 2010 and later
- Category
- Statistical functions
- Returns
- The position of the number in the list, as a whole number.
- Google Sheets
- Also available in Google Sheets.
Syntax
=RANK.EQ(number, ref, [order])
| Argument | Required? | What it is |
|---|---|---|
number | Required | The value to rank, usually the cell on the same row. |
ref | Required | The full list of values to rank against. Lock it with $ before filling down. |
order | Optional | 0 or omitted ranks the largest as 1; any other number ranks the smallest as 1. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =RANK.EQ(B2,B2:B7) | 2 | Ana is second; Lee has the top sales |
| F3 | =RANK.EQ(B2,B2:B7,1) | 5 | Ascending order: rank 1 is the smallest value |
| F4 | =RANK.EQ(B6,B2:B7) | 4 | Mo and Raj tie for 4th, so no one is ranked 5th |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Rep | Sales | Check | Result | ||
| Ana | $48,200 | Ana, highest = 1 | 2 | ||
| Raj | $39,500 | Ana, lowest = 1 | 5 | ||
| Lee | $51,750 | Mo, tied with Raj | 4 | ||
| Kim | $44,100 | ||||
| Mo | $39,500 | ||||
| Ivy | $28,900 |
Practice RANK.EQ
From lesson 34, RANK, LARGE & SMALL. Graded instantly, then re-checked on changed data.
Rank the team
C2, rank each rep's sales (1 = highest). Fill down to C9.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Ranks repeat or jump after filling down | The ref range shifted. Lock it: =RANK.EQ(B2,$B$2:$B$7). |
| #N/A | The number isn't in the ref list, often because ref doesn't cover the whole column of values. |
| Ties share a rank and leave a gap | That's how RANK.EQ works. Use RANK.AVG for an average rank, or add COUNTIF($B$2:B2,B2)-1 to break ties by order. |
Tips
- To rank within a group, such as by region, use COUNTIFS: =COUNTIFS(region,region_cell,sales,">"&sales_cell)+1.
Questions
What is the difference between RANK and RANK.EQ?
They return the same result. RANK.EQ is the name introduced in Excel 2010; RANK is kept for compatibility.
How do I rank from lowest to highest?
Set the third argument to 1: =RANK.EQ(B2,$B$2:$B$7,1).
How do I rank without ties?
Add a tie-breaker: =RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1 gives the first occurrence the better rank.
Lessons that use RANK.EQ
Answers that use RANK.EQ
In other languages
RANK.EQ is JERARQUIA.EQV in Spanish Excel and RANG.GLEICH in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .