Excel’s RANK.AVG gives tied values the average of the rank positions they occupy; RANK.EQ gives every tied value the same rank, leaving a gap after the tie. Use RANK.EQ for the usual competition-style ranking, and choose RANK.AVG when a shared average position is more useful.
The key difference: how ties are ranked
Both functions compare one number with a range of numbers and return its position in that set. Their results are identical when there are no ties. They differ only when two or more values are equal: RANK.EQ assigns the tied values the same rank, while RANK.AVG averages the positions those values would otherwise have occupied.
For example, consider scores of 100, 90, 90, and 80, ranked from highest to lowest. The two 90s would occupy positions two and three. RANK.EQ returns 2 for each 90, and the 80 receives rank 4. RANK.AVG returns 2.5 for each 90, because the average of positions two and three is 2.5; the 80 still receives rank 4. The shared rank does not mean the scores themselves are averaged.
That distinction matters when you interpret, display, or use ranks in later calculations. A rank of 2.5 is a valid result from RANK.AVG, not an error or an indication that the underlying score is fractional.
How the functions work
The syntax for each function has the same three arguments: the value to rank, the range to compare it against, and an optional sort direction. With scores in A2:A5, for example, the first two arguments identify the score in A2 and the full comparison range. The third argument controls whether the largest or smallest number ranks first.
- Omit the third argument, or use 0, to rank in descending order: the largest value is rank 1.
- Use any nonzero value, commonly 1, to rank in ascending order: the smallest value is rank 1.
- The comparison range should include all values that belong in the ranking, not just the value on the current row.
With scores in A2:A5 and the comparison range fixed so the formula can be copied down, use this for average tie positions:
=RANK.AVG(A2,$A$2:$A$5,0)
With the same values in A2:A5 and the same descending direction, use this for equal ranks on ties:
=RANK.EQ(A2,$A$2:$A$5,0)
In the example set 100, 90, 90, and 80, the first formula returns 1, 2.5, 2.5, and 4 as it is copied down; the second returns 1, 2, 2, and 4. The dollar signs make the comparison range absolute, so it stays A2:A5 when you fill the formula into other rows. The reference to A2 is relative, so it changes to A3, A4, and so on.
What ascending order changes
The optional order argument changes which end of the scale gets rank 1, not the rule for ties. If a lower time is better, for instance, ascending ranking puts the smallest time first. Tied times still share a rank in RANK.EQ, while RANK.AVG averages their occupied positions.
With times in A2:A5 and the full time range in A2:A5, this formula ranks the smallest time first:
=RANK.EQ(A2,$A$2:$A$5,1)
For a set of times of 12, 14, 14, and 18, the two 14s receive rank 2 with RANK.EQ and rank 2.5 with RANK.AVG. The 18 receives rank 4 in either case. Be explicit about the direction: omitting the third argument means descending order, which may be the opposite of what makes sense for a time, cost, or error measure.
Choosing the right function for your data
Use RANK.EQ when a tie should preserve a shared place and the next rank should reflect the number of entries ahead. This is common in scoreboards and contest standings. If two people tie for second, both are second and the next person is fourth. The gap communicates that two entries occupy the first two positions.
Use RANK.AVG when you want tied entries to share the mean of their positions. It can be appropriate for statistical summaries or analyses in which a tie should be represented by a middle position rather than a competition-style gap. However, a fractional rank is not automatically more fair or more statistically meaningful. Choose based on how the ranks will be explained and used.
- For a leaderboard that displays shared places and skips the next place after a tie, use
RANK.EQ. - For a ranking where tied entries should receive the average of the positions they cover, use
RANK.AVG. - If you need every row to have a distinct position, neither function alone resolves ties. Decide on a meaningful secondary sort rule, such as date or another score.
Rank values are ordinal: they tell you order, not the size of the difference between values. A rank of 1 does not show whether the top score barely beat second place or exceeded it by a wide margin. Keep the original measure alongside the rank when readers need that context.
Using ranks in a worksheet
A practical layout puts each item in one row, the value being compared in one column, and the calculated rank in another. For example, if names are in A2:A5 and scores are in B2:B5, enter the ranking formula in C2 and fill it down. Use an absolute reference for the full score range so every row is ranked against the same group.
With names in A2:A5, scores in B2:B5, and the formula being entered in C2, this ranks the score in B2 from highest to lowest using average ranks for ties:
=RANK.AVG(B2,$B$2:$B$5,0)
With the same names, scores, and destination cell, this instead gives tied scores equal ranks:
=RANK.EQ(B2,$B$2:$B$5,0)
After filling down, you can sort the table by score or rank to make the order easier to read. If you sort, keep each row’s name, score, and rank together. A rank formula updates when its referenced values change, but the worksheet’s displayed row order does not necessarily become a sorted leaderboard by itself.
Common problems and misconceptions
- The range shifts when copied. If the formula compares B2 with B2:B5 and you fill down, the comparison range may shift. Use
$B$2:$B$5when every row should be compared with the same set. - The result looks like a skipped rank. With
RANK.EQ, this is expected. If two entries tie at rank 2, the next rank is 4 because three entries occupy the first three positions. - The result is fractional. With
RANK.AVG, this is expected when a tie covers an even-numbered range of positions, such as positions two and three. The function averages those positions. - The ranking direction seems backward. The default ranks the largest value first. Add a nonzero order argument to put the smallest value first.
- Values that look equal do not tie. Two displayed numbers may be rounded versions of different underlying values. Check their full precision or apply a consistent rounding method before ranking if the business rule says displayed values should count as equal.
Text entries and logical values in the reference range are not treated as numbers for ranking. Make sure the values you intend to compare are numeric; numbers stored as text can lead to results that do not match expectations. Also check that the formula points to the intended range, particularly if new rows are added later.
How RANK.EQ relates to the older RANK function
RANK.EQ and RANK.AVG were introduced in Excel 2010. The older RANK function remains available for compatibility and behaves like RANK.EQ for ties. It does not average tied positions. Newer workbooks often use RANK.EQ because its name makes the tie behavior explicit.
If you share a workbook with users of Excel versions older than Excel 2010, check compatibility before relying on RANK.EQ or RANK.AVG; an older version may not recognize those function names. If the workbook must support an older installation, the legacy RANK function can provide the equal-rank behavior, but it cannot substitute for RANK.AVG where average tie ranks are required.
When you need a unique order
Neither function is designed to break ties. If two records have equal scores, assigning each a different rank requires an extra rule. A simple option is to use row order as the tie-breaker, though that makes the result depend on the current arrangement of the data. A more meaningful rule might use a secondary metric, such as fewer errors or an earlier completion time.
With scores in B2:B5, the formula entered in C2 below ranks descending and uses the order of matching rows to give repeated scores successive positions. Copy it down through C5:
=RANK.EQ(B2,$B$2:$B$5,0)+COUNTIF($B$2:B2,B2)-1
For scores 100, 90, 90, and 80 in that row order, the results are 1, 2, 3, and 4. This is not the same as saying the tied scores are inherently different; it is an explicit tie-breaking convention. If row order should not decide the outcome, rank by the secondary criterion or sort the data using both criteria instead.
Frequently Asked Questions
Are RANK.AVG and RANK.EQ the same when there are no ties?
Yes. If every value in the comparison range is distinct, both functions return the same rank for a given value and sort direction. Their difference appears only when values tie.
Does RANK.AVG give a better or fairer result?
Not automatically. It gives tied values the average of the positions they occupy. Whether that is preferable depends on the ranking convention and how the result will be used; competition standings commonly use equal ranks instead.
Why does RANK.EQ skip a number?
It assigns the same rank to each tied value, while counting all entries when determining the next position. Two entries tied at rank 2 occupy two places, so the next entry is rank 4.
Can I rank the smallest value as number one?
Yes. Supply a nonzero third argument, such as 1, for ascending order. The smallest value then ranks first; the function’s tie rule remains unchanged.
What does the old RANK function do with ties?
RANK gives tied values the same rank, like RANK.EQ. It does not return the average tie position that RANK.AVG calculates.
Related
- Excel Number Format: How to Show Zero as Blank Cells
- Excel Number Format: Red Negative Numbers in Brackets
- Excel Number Format for Negative Numbers in Parentheses
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel SUM: How to Exclude Hidden Rows and Filtered RowsYes. To sum visible rows while excluding both filtered-out rows and rows hidden manually, use SUBTOTAL with function…
- Link a PivotChart to a Different PivotTable in ExcelYou generally cannot retarget an existing Excel PivotChart directly to a different PivotTable. To make a chart use…
- Excel Regression Line: Add, Format, and Interpret ItYes. In Excel for Microsoft 365 on Windows, create or select an XY scatter chart, then choose Chart…
- How to Add Page Numbers at the Bottom of a Word DocumentTo add page numbers at the bottom of a Word document, open the Insert tab, choose Page Number…
- How to Insert an Image in Excel (Step-by-Step)To insert an image in Excel for Microsoft 365 on Windows, select the worksheet, choose Insert > Pictures…
- Excel Data Validation Source: How to Find ItTo find a dropdown list’s source in Excel for Microsoft 365 on Windows, select a cell with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.