RANK

Statistical Functions

(4.8/5)

Returns the rank of a number in a list of numbers. RANK determines the position of a value when the list is sorted. Higher values get higher ranks. For duplicate values, RANK assigns the same rank and skips subsequent ranks. Use RANK.EQ for exact match (same behavior) or RANK.AVG for average rank of duplicates. Essential for ranking analysis, sorting data, and determining relative position.

Interactive Formula Tester

Value,Ref,Order:

Formula:

=RANK("")

Complete Theory & Understanding

Master the fundamentals of Excel RANK function

Core Concept

The RANK function returns the rank of a number in a list of numbers. RANK determines the position of a value when the list is sorted. With order=0 or omitted, ranking is descending (largest value = rank 1). With order≠0, ranking is ascending (smallest value = rank 1). For duplicate values, RANK assigns the same rank to all duplicates and skips subsequent ranks (e.g., if two values tie for rank 3, the next value gets rank 5). RANK is equivalent to RANK.EQ. Use RANK.AVG if you want average ranks for duplicates. Essential for ranking analysis, sorting data, determining relative position, and competitive analysis.

Why Use RANK?

Rank values or performers
Rank competitors or scores
Determine sort position
Find position in dataset

Key Characteristics

Ranking Order

0 = descending, non-zero = ascending

RANK(value, range, 0) = descending

Duplicate Handling

Same rank, skip subsequent

Duplicates get same rank, next skips

Equals RANK.EQ

RANK = RANK.EQ

Both have same behavior

Relative Position

Position in sorted list

RANK determines relative position

Function Anatomy

=RANK(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Ranking Analysis

Rank values or performers

Competitive Analysis

Rank competitors or scores

Data Sorting

Determine sort position

Relative Position

Find position in dataset

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=RANK(number, ref, order)

Required

number:

The number whose rank you want to find.

Required

ref:

Array or reference to a list of numbers.

Optional

order:

How to rank number: 0 or omitted = descending (largest = 1), non-zero = ascending (smallest = 1).

Returns

Return Value:

The rank of the number

Description: Returns the rank of a number in a list of numbers

Interactive Examples

Basic RANK (Descending)

Rank in descending order

Input Text:

"25, A1:A10 (10,20,25,30,40)"

Formula:

=RANK(25, A1:A10, 0)

Result:

Returns 3. 25 is the 3rd largest value. RANK with order=0 ranks descending (largest = rank 1).

VBA Implementation & Automation

Basic RANK in VBA

Use RANK function in VBA

' Basic RANK in VBA
Range("C1").Value = Application.WorksheetFunction.Rank(Range("B1").Value, Range("A1:A10"), 0)
' Returns: Rank of B1 in A1:A10

' Find rank of a value
Sub FindRank()
    Dim rankValue As Long
    Dim valueToRank As Double
    valueToRank = Range("B1").Value
    rankValue = Application.WorksheetFunction.Rank(valueToRank, Range("A1:A10"), 0)
    Range("C1").Value = rankValue
End Sub

' Rank in descending order
Sub RankDescending()
    Dim rankValue As Long
    rankValue = Application.WorksheetFunction.Rank(Range("B1").Value, Range("A1:A10"), 0)
    Range("C1").Value = "Descending Rank: " & rankValue
End Sub

' Rank in ascending order
Sub RankAscending()
    Dim rankValue As Long
    rankValue = Application.WorksheetFunction.Rank(Range("B1").Value, Range("A1:A10"), 1)
    Range("C1").Value = "Ascending Rank: " & rankValue
End Sub

' Compare RANK with RANK.EQ and RANK.AVG
Sub CompareRankFunctions()
    Dim valueToRank As Double
    Dim rankValue As Long
    Dim rankEQValue As Long
    Dim rankAVGValue As Double
    
    valueToRank = Range("B1").Value
    rankValue = Application.WorksheetFunction.Rank(valueToRank, Range("A1:A10"), 0)
    rankEQValue = Application.WorksheetFunction.Rank_Avg(valueToRank, Range("A1:A10"), False)
    rankAVGValue = Application.WorksheetFunction.Rank_Avg(valueToRank, Range("A1:A10"), False)
    
    Range("C1").Value = "RANK: " & rankValue
    Range("C2").Value = "RANK.EQ: " & rankEQValue
    Range("C3").Value = "RANK.AVG: " & rankAVGValue
    ' RANK and RANK.EQ should be equal
End Sub

' Rank multiple values
Sub RankMultipleValues()
    Dim i As Integer
    Dim rankValue As Long
    For i = 1 To 10
        rankValue = Application.WorksheetFunction.Rank(Range("A" & i).Value, Range("A1:A10"), 0)
        Range("B" & i).Value = rankValue
    Next i
End Sub

Business Applications

Ranking Analysis

Rank values or performers

=RANK(value, range, 0)

Competitive Analysis

Rank competitors or scores

=RANK(score, all_scores, 0)

Data Sorting

Determine sort position

=RANK(value, data_range, 0)

Relative Position

Find position in dataset

=RANK(value, dataset, 1)

Common Issues & Solutions

#N/A Error

RANK returns #N/A

Ensure value exists in reference range

Solution: This occurs when the number is not found in the reference. Ensure the number exists in the ref range. Check for data type mismatches (text vs number).

Wrong Rank Direction

Getting unexpected rank

Order=0: descending, Order≠0: ascending

Solution: Check the order parameter. Order=0 (or omitted) ranks descending (largest=1). Order≠0 ranks ascending (smallest=1). Verify your intended ranking direction.

Duplicates Skip Ranks

Ranks skip numbers (3, 3, 5)

RANK skips ranks for duplicates

Solution: This is expected. RANK assigns same rank to duplicates and skips subsequent ranks. If you want continuous ranks (3, 3, 4), you need a different approach or use helper formulas.

RANK vs RANK.AVG

Uncertainty about which to use

RANK skips, RANK.AVG averages

Solution: RANK (and RANK.EQ) assign same rank to duplicates and skip. RANK.AVG averages ranks for duplicates and continues. Use RANK for standard ranking, RANK.AVG for average ranking of duplicates.