Skip to content

Excel function · Statistics

Excel RANK.EQ function

RANK.EQ tells you where a number stands in a list: a sales ranking, exam positions, the fastest supplier by delivery time. Tied values get the same rank.

In Turkish Excel: RANK.EŞİT

What it does

RANK.EQ needs three things: the number to rank, the list to compare it with and the direction. If the direction is omitted or 0, the largest value ranks 1st (sales, scores). With 1, the smallest value ranks 1st (time, cost, number of errors).

Tied values share a rank and the next rank is skipped: if two students are 2nd, the next one is 4th. If ties should get the average rank, use RANK.AVG. To break ties and give every row its own rank, use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1; whichever comes first in the list goes ahead.

Lock the list reference as $B$2:$B$9; otherwise the range shifts when you fill the formula down and the ranks go wrong. The older RANK function returns the same result and is kept for compatibility.

Syntax

English Excel=RANK.EQ(number, ref, [order])
Turkish Excel=RANK.EŞİT(sayı; başv; [sıra])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
numberRequiredThe number or cell to rank. It must be in ref.
refRequiredThe list to compare with, e.g. $B$2:$B$9. Cells that are not numbers are ignored.
orderOptional0 or omitted: largest to smallest, so the largest value ranks 1st. 1: smallest to largest, so the smallest value ranks 1st.

Examples

1Rank by sales

The third argument is left out so that the highest sale ranks 1st. The list range is locked with $.

C2fx=RANK.EQ(B2,$B$2:$B$6)
Rank by sales: example table
ABC
1RepSalesRank
2Emma48,5004
3Liam61,2001
4Olivia52,3003
5Noah29,7505
6Ava57,8002
  • C2 (filled down to C6)=RANK.EQ(B2,$B$2:$B$6)

Liam's rank is 1, Ava's 2 and Noah's 5.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Make the shortest delivery time rank first

For delivery times, smaller is better. With 1 as the third argument, the shortest time takes 1st place.

C2fx=RANK.EQ(B2,$B$2:$B$5,1)
Make the shortest delivery time rank first: example table
ABC
1CourierAverage DaysRank
2Swift Post2.42
3Northern Freight3.13
4Express Carriers1.81
5Hillside Haulage4.64
  • C2 (filled down to C5)=RANK.EQ(B2,$B$2:$B$5,1)

Express Carriers gets rank 1 and Hillside Haulage rank 4. Without the third argument, the order would be reversed.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Give tied scores their own rank

Column C shows RANK.EQ's own result and column D the rank with ties broken. The range $B$2:B2 grows by one row each time; COUNTIF counts how many times the score has appeared up to that row.

C2fx=RANK.EQ(B2,$B$2:$B$7)
Give tied scores their own rank: example table
ABCD
1StudentScoreRankUnique Rank
2Mia9022
3Lucas8544
4Ella9023
5Jack7866
6Grace8545
7Henry9511
  • C2 (filled down to C7)=RANK.EQ(B2,$B$2:$B$7)
  • D2 (filled down to D7)=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Mia and Ella share rank 2 in column C, and rank 3 is skipped. In column D Mia is 2 and Ella 3; Lucas and Grace are split into 4 and 5.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: number is not in ref; usually the list range has shifted or the value lies outside the list.

Fix: Lock the list reference with $ and make sure the number is in the list.

Ranks drift

Cause: ref was not locked before filling down, so each row was compared with a different range.

Fix: Use $B$2:$B$9 instead of B2:B9.

A rank is skipped

Cause: Tied values got the same rank and the next rank number was skipped (1, 2, 2, 4).

Fix: That is how RANK.EQ works; if every row needs its own rank, break the tie with COUNTIF.

Good to know

  • For rankings where the lowest value is best (time, cost), remember to pass 1 as the third argument.
  • Instead of sorting the list by the rank column, build a sorted copy with SORT in Microsoft 365; the source list stays as it is.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Rank column to the sales list that shows each rep's sales ranking

Convert to table

Bring your sales list into the tool and type this. Nexell adds the Rank column with real Excel formulas and shows it in the preview first; in the downloaded file, the ranking changes when the sales change.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I give tied values different ranks in Excel?

Use =RANK.EQ(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1 and fill it down. COUNTIF counts how many times the value has appeared up to that row, so the second occurrence moves one place down.

What is the difference between RANK.EQ and RANK.AVG?

If two values tie for 2nd place, RANK.EQ gives both 2. RANK.AVG gives both 2.5, the average of the 2nd and 3rd places.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free