RANK.AVG in Excel: Average Tied Ranks and Preserve Ranking Direction

• Introduced: the Microsoft function catalogue marks RANK.AVG as an Excel 2010 addition; an exact first build or preview date is not established here.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016, Excel Web App.
• Checked: October 14, 2026; official RANK.AVG reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

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

number is the value being ranked and ref supplies the comparison list. Microsoft states that nonnumeric reference values are ignored. Zero or an omitted order gives descending ranks; any nonzero numeric order gives ascending ranks.

Lock the reference range when filling a rank formula down. An expanding or shrinking comparison group can change a rank even when the row’s own value is unchanged.

Enter 90, 80, 80 and 60 in A2:A5. In B2 enter and fill down:

=RANK.AVG(A2,$A$2:$A$5,0)

Expected ranks are 1, 2.5, 2.5 and 4. The two scores of 80 occupy positions 2 and 3, so each gets their average, (2+3)/2.

With ascending order 1, the expected ranks become 4, 2.5, 2.5 and 1. The best direction depends on the task: a high score might be desirable, while a low completion time might be preferable.

In a separate C2:C6 range enter 100, 90, 90, 90 and 70. A descending rank for 90 is 3, the average of occupied positions 2,3,4.

=RANK.AVG(90,C2:C6,0)

The full rank column is 1, 3, 3, 3 and 5. The value 70 retains position 5; averaging a tie does not compress all later positions into a dense ranking.

The ranks sum to 15, the same total as 1+2+3+4+5. This is a useful arithmetic control for the complete numeric comparison group.

For the first dataset 90,80,80,60, RANK.EQ produces 1, 2, 2 and 4, while RANK.AVG produces 1, 2.5, 2.5 and 4.

Both functions preserve the fact that the two 80 values are tied. They differ in the number assigned to that tied group. Neither chooses a unique winner between the two records.

Keep a separate stable identifier when rows represent people, products or transactions. A rank can change whenever the comparison group changes and should not be used as an identity key.

Against 90,80,80,60, a rule =RANK.AVG(A2,$A$2:$A$5,0)<=2 selects only the 90 row, because the tied scores have rank 2.5. Raising the cutoff to 2.5 selects three rows.

There is no cutoff that selects exactly one of the tied 80 rows using RANK.AVG alone. If exactly two records must be selected, define an additional tie-break rule rather than pretending the average rank identifies the second record uniquely.

This is especially important when a numerical cutoff is copied from a competition-rank report. The same cutoff can select a different set under an average-rank convention.

Enter 10.01, 10.04 and 11 in F2:F4. With full stored precision, their descending average ranks are 3, 2 and 1.

If the stated policy instead ranks one-decimal measurements, create G2 with =ROUND(F2,1) and fill down. The helper values are 10, 10 and 11. Ranking that helper range gives 2.5, 2.5 and 1.

Changing a cell’s display to one decimal is not the same as creating those rounded numeric inputs. Retain both source and helper columns so the policy can be inspected later.

For a separate range containing numeric 10, numeric 20, formula ="30", formula =TRUE() and a genuinely empty cell, the documented numeric reference group consists of 10 and 20.

A descending rank for 10 is therefore expected to be 2. The displayed text 30 does not become a third numeric observation merely because it looks like a quantity.

The dedicated reference does not supply a complete error-code matrix for a number absent from the list, an error-containing reference, malformed arguments or text-number lookup coercion. Those cases remain unverified here. Every primary ranked value is present in its numeric comparison group.

For ascending ranks, writing 1 is clearer than relying on an unusual nonzero order such as -1, even though the documented direction rule covers nonzero numbers. A similarly named function in another spreadsheet needs its own tie and default-order checks.

• Microsoft RANK.AVG reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.