RANK.EQ, RANK and RANK.AVG functions in Excel

RANK.EQ gives the position of a number within a list, with equal numbers sharing the same rank.

A rank says where a value stands among the others: first, second, third. RANK.EQ works it out without sorting the list, so each student's position can sit beside the marks in their original order.

Syntax

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

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

Argument	What it is
`number`	The number whose rank you want.
`ref`	The list of numbers to rank it against.
`order`	0 or left out: the largest number is rank 1. Any other value: the smallest number is rank 1.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=RANK.EQ(C4,$C$2:$C$9)
Result: 1
Meena has the highest Maths mark, so her rank is 1.
=RANK.EQ(C2,$C$2:$C$9)
Result: 3
Asha has 78, the third highest.
=RANK.EQ(C9,$C$2:$C$9)
Result: 3
Imran also has 78. He shares rank 3 with Asha.
=RANK.EQ(C6,$C$2:$C$9)
Result: 5
After two students at rank 3, the next rank is 5. Rank 4 is skipped.
=RANK.AVG(C2,$C$2:$C$9)
Result: 3.5
RANK.AVG gives tied values the average of the places they occupy: 3 and 4 make 3.5.
=RANK.EQ(C5,$C$2:$C$9,1)
Result: 1
Order 1 ranks from the bottom: the lowest mark is rank 1.
=RANK.EQ(C2,$C$2:$C$9)+COUNTIF($C$2:C2,C2)-1
Result: 3
A tie-breaker that gives every row a different rank, by adding the number of earlier rows with the same mark.

How it works

RANK.EQ compares one number with all the numbers in the list and counts how many are larger. One more than that count is the rank. The largest number gets rank 1, unless you set the third argument to 1, in which case the smallest does. Rank from the smallest for race times and golf scores, where low is good.

Lock the list

The rank formula is always copied down a column. The list must stay fixed while the single cell moves, so the range needs dollar signs: $C$2:$C$9. Without them the list slides down with each copy and the ranks are wrong, with no error to warn you. This is the commonest mistake with RANK.

Ties

Equal values receive the same rank, and the following rank is skipped. With two students at rank 3, there is no rank 4, and the next student is 5th. This is the usual convention in sports and examination results.

Marks	RANK.EQ	RANK.AVG
92	1	1
88	2	2
78	3	3.5
78	3	3.5
67	5	5

RANK.AVG gives tied values the average of the places they would fill. Statisticians use it. For a class list, RANK.EQ is what people expect.

Ranks without gaps

Some results are published as 1, 2, 3, 3, 4, with no place skipped. This is called dense ranking, and Excel has no function for it. In Excel 2021 and Microsoft 365, count the distinct values that are greater and add one:

=COUNT(UNIQUE(FILTER($C$2:$C$9,$C$2:$C$9>C2)))+1

For the top value, FILTER finds nothing and returns an error, so wrap the count part in IFERROR with 0.

Ranks with no ties at all

When every row must have a different rank, for example to pull out a top-ten list with INDEX, break ties by position, as in the last example. The first of two equal values keeps its rank and the second gets the next one.

Ranking inside groups

RANK.EQ ranks against the whole list. To rank each student within their own class, count the students of the same class with a higher mark:

=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1

The same pattern works for any group and any number of conditions.

The old RANK function

Before Excel 2010 the function was called simply RANK. It still works and behaves exactly as RANK.EQ. You will see it in older workbooks. Use RANK.EQ in new work.

Common mistakes

No dollar signs on the list.
#N/A when the number is not in the list at all. RANK.EQ can only rank a value that appears in the list.
Ranking from the wrong end.
Text or empty cells in the list are ignored, so a student with no mark has no rank and the formula on that row shows #N/A.

In Google Sheets

Google Sheets has RANK, RANK.EQ and RANK.AVG, working the same way.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.