fx
FunctionPedia
Quick Answer

RANK Function

Gives the rank of a value in a dataset, with ties sharing a rank.

✓ Excel✓ Google SheetsExcel All versions

Syntax

RANK
(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

Rank of 30 in a descending list

=RANK(30, {10,20,30,40,50})
Result3

In descending order 50=1, 40=2, 30=3, so 30 is rank 3.

Advanced Examples

Example 1: Tied values share the same rank

Two equal entries in the list

RANK assigns the same rank to equal values and skips the next rank.

=RANK(20, {10,20,20,30})
Result: 2
The 20s are the 2nd and 3rd largest, so both get rank 2; the 30 then gets rank 4.

Example 2: Ascending order ranking

Smallest value should be first

Set order to 1 so the smallest number gets rank 1.

=RANK(10, {10,20,30,40,50}, 1)
Result: 1
In ascending order the smallest value, 10, receives rank 1.

How RANK Works

RANK compares a value to the other numbers in the reference list. By default (order 0) it ranks from largest to smallest, giving the largest value rank 1; order 1 ranks smallest to largest. Equal values receive the same rank, and the next rank is skipped (e.g. two values at rank 2 leave the next value at rank 4). This tie behavior matches RANK.EQ. In Microsoft 365 use RANK.EQ or RANK.AVG instead.

1
Select the result cell
Click where you want the rank.
2
Enter the formula
Type =RANK(number, range, [order]).
3
Press Enter
The rank of the value is returned.

Important Notes & Limitations

  • Tied values share a rank and subsequent ranks are skipped, which can distort percentile-style analysis.

  • Cannot give fractional (averaged) ranks for ties; use RANK.AVG for that.

  • Considers only numbers; text in the reference is ignored.

Common Errors & Fixes

#N/A errorThe number is not found in the reference list (or the list has no numbers).

Fix: Ensure the value exists in the reference, or rank against the actual data range.

Wrong ranking directionForgetting the order argument defaults to descending.

Fix: Add 1 as the third argument for ascending ranking.

Download Practice File

Practice RANK with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the RANK 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

What is the difference between RANK and RANK.EQ?
They behave identically. RANK.EQ is the name added in Excel 2010; RANK is kept for compatibility.
How do I rank ties with an average rank?
Use RANK.AVG, which assigns tied values their average rank (e.g. 2.5 instead of 2).
Why are ranks skipped after a tie?
RANK gives both tied values the same rank, so if two share rank 2, the next distinct value becomes rank 4.