fx
FunctionPedia
Quick Answer

RANK.AVG Function

Ranks a value; ties receive the average of the ranks they occupy.

✓ Excel✓ Google SheetsExcel Excel 2010+

Syntax

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

Parameters

ParameterDescriptionRequired
numberThe value whose rank you want to find.Required
refThe list (range or array) of numbers to rank against.Required
orderOptional. 0 or omitted = descending (largest = rank 1); any nonzero value = ascending (smallest = rank 1).Optional

Basic Example

Two equal values get the average of their ranks

=RANK.AVG(20, {10,20,20,30})
Result2.5

The 20s would be ranks 2 and 3, so each receives their average, 2.5.

Advanced Examples

Example 1: Three-way tie averages three ranks

Three identical entries

With three equal values the rank is the mean of 2, 3, and 4.

=RANK.AVG(15, {10,15,15,15,20})
Result: 3
The 15s occupy ranks 2, 3, and 4, whose average is 3.

Example 2: Ascending order with a tie

Smallest-first ranking

order = 1 reverses the order but ties still get averaged ranks.

=RANK.AVG(20, {10,20,20,30}, 1)
Result: 2.5
In ascending order the 20s are ranks 2 and 3, averaged to 2.5.

How RANK.AVG Works

RANK.AVG ranks a value against the reference list, defaulting to descending order (largest = rank 1). When several values tie, all of them receive the arithmetic mean of the ranks they would have occupied, so no ranks are skipped. This contrasts with RANK.EQ, which gives ties the same whole rank and skips the following one. It returns #N/A if the value is not found.

1
Select the result cell
Click where you want the rank.
2
Enter the formula
Type =RANK.AVG(number, range, [order]).
3
Press Enter
The (possibly fractional) rank appears.

Important Notes & Limitations

  • Returns fractional ranks for ties, which may need rounding for display.

  • Only ranks against numbers; text in the reference is ignored.

  • In pre-2010 Excel this function is unavailable (use RANK).

Common Errors & Fixes

#N/A errorThe number is not present in the reference list.

Fix: Ensure the value is included in the range being ranked.

Unexpected decimal rankA tie produced an averaged fractional rank by design.

Fix: This is expected; round the result with ROUND if you need a whole number for display.

Download Practice File

Practice RANK.AVG with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the RANK.AVG function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

How is RANK.AVG different from RANK.EQ?
RANK.EQ gives ties the same whole rank and skips the next rank; RANK.AVG gives ties their average rank, so no ranks are skipped.
Why is my rank a decimal?
Because of a tie. RANK.AVG averages the ranks the tied values occupy, producing a fractional result.
Which should I use for statistical work?
Use RANK.AVG when ties should not distort subsequent rank-based calculations, since it preserves the rank count.