exceltestpractice
On the clock 0:00Free practice test
Practice testsLearnFunctionsShortcutsPricingFree practice test

Function guides ยท Statistical

RANK.EQ function

Returns the rank of a number within a list, with ties sharing the same rank.

Syntax

=RANK.EQ(number, ref, [order])
ArgumentWhat it is
numberThe value to rank.
refThe range of numbers to rank against.
orderOptional. 0 or omitted ranks from largest (1 = highest). Any other number ranks from smallest.

What RANK.EQ does

RANK.EQ tells you where a number stands in a list. The highest value is rank 1 unless you ask for the order to be reversed.

The EQ in the name stands for equal. If two values tie, they get the same rank, and the next rank is skipped. Two numbers tied for first mean the next number is third. A rank column is one of the most common additions to a results table.

The third argument decides direction. Leave it out, or use 0, for largest first. Anything else is smallest first.

A rank column pairs naturally with sorting. Sort the table by the rank column and the leaders move to the top, and the formulas keep working because the range is locked. For a top five, a filter on rank less than or equal to 5 is quicker than any formula.

When ties must be broken, COUNTIF adds a position-based nudge. Adding COUNTIF($B$2:B2,B2)-1 to the rank gives equal values consecutive ranks in the order they appear in the list.

RANK.EQ examples

1. Rank sales from highest

Rank sales from highest. The formula =RANK.EQ(B4,$B$2:$B$5) in cell C4 returns 1.
ABC
1RepUnits
2Dana410
3Luis295
4Mei5201
5Omar180

Mei's 520 is the largest of the four numbers, so it ranks 1. With order omitted, the largest value gets rank 1.

2. Rank from lowest, for times or costs

Rank from lowest, for times or costs. The formula =RANK.EQ(B3,$B$2:$B$4,1) in cell C3 returns 1.
ABC
1RunnerSeconds
2Ana61.2
3Ben58.91
4Chloe60.4

When smaller is better, such as a race time, pass 1 as the third argument. Ben ran 58.9, the fastest, so he ranks 1. Without the 1, he would rank 3.

3. Ties share a rank and skip the next

Ties share a rank and skip the next. The formula =RANK.EQ(B4,$B$2:$B$4) in cell C4 returns 3.
ABC
1StudentScore
2Ana90
3Ben90
4Chloe853

Two scores of 90 both rank 1. The next score of 85 ranks 3, because rank 2 is skipped. A tie-breaker is needed if every position must be distinct.

Common RANK.EQ mistakes

  • Forgetting to lock the range. Filling down with B2:B5 slides the list and every rank is wrong. Use $B$2:$B$5.
  • Ranking the wrong direction. Omitted order ranks from the largest. For times, costs and golf scores, pass 1.
  • Expecting unique ranks. Ties share a rank and leave a gap. Add COUNTIF($B$2:B2,B2)-1 to break ties by position.
  • A number that is not in the list. If the value is not in ref, the result is #N/A.
  • Confusing RANK, RANK.EQ and RANK.AVG. RANK and RANK.EQ behave the same. RANK.AVG gives tied values the average of their ranks.

RANK.EQ on Excel job tests

Typical task: "add a position column to the results table", often followed by a sort on that column or a lookup of whoever ranks first.

Lock the list with dollar signs, decide whether bigger is better (race times and costs rank ascending, so pass 1 as the third argument), and look at how ties come out. If the prompt wants a tie-breaker such as earlier submission, add a COUNTIF term or a helper column.

The older name RANK still works. After filling down, change one score in the data and see that the ranks follow.

  • LARGEReturns the k-th largest value in a range.
  • SMALLReturns the k-th smallest value in a range.
  • COUNTIFCounts the cells in a range that meet one condition.
  • SORTReturns a sorted copy of a range, spilling the results into neighboring cells.
  • MATCHReturns the position of a value within a row or column.

RANK.EQ questions

What is the difference between RANK and RANK.EQ?
They give identical results. RANK is the older name kept for compatibility.
How do I rank from smallest to largest?
Pass any non-zero number, usually 1, as the third argument.
How do I avoid tied ranks?
Add a tie-breaker: =RANK.EQ(B2,$B$2:$B$20)+COUNTIF($B$2:B2,B2)-1.
What is RANK.AVG?
It gives tied values the average of the ranks they would have taken, such as 1.5 for two values tied for first.

Practice RANK.EQ

The lesson RANK, RANK.EQ and RANK.AVG (module 20 of Analyst) has checked RANK.EQ exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.

Try a 5-item Excel test sample