PERCENTRANK

Statistical Functions

(4.8/5)

Returns the rank of a value in a data set as a percentage. PERCENTRANK finds the percentile rank of a value (0 to 1). PERCENTRANK uses the inclusive method (same as PERCENTILE.INC). Returns where a value stands relative to other values. Essential for percentile ranking, comparing values to a data set, and understanding relative position.

Interactive Formula Tester

Values,TestValue:

Formula:

=PERCENTRANK("10, 20, 30, 40, 50, 35")

Complete Theory & Understanding

Master the fundamentals of Excel PERCENTRANK function

Core Concept

The PERCENTRANK function returns the rank of a value in a data set as a percentage (0 to 1). PERCENTRANK is the inverse of PERCENTILE - while PERCENTILE finds the value at a given percentile, PERCENTRANK finds the percentile rank of a given value. PERCENTRANK uses the inclusive method and is equivalent to PERCENTRANK.INC. It returns where a value stands relative to other values in the data set. If the value is not exactly in the array, PERCENTRANK interpolates the percentile rank. Essential for percentile ranking, comparing values to a data set, understanding relative position, and inverse percentile calculations.

Why Use PERCENTRANK?

Find percentile rank of values
Compare values to data set
Find rank instead of value
Understand value position

Key Characteristics

Rank 0 to 1

Returns percentile rank

0.9 = 90th percentile

Inclusive Method

Uses inclusive method

PERCENTRANK = PERCENTRANK.INC

Inverse of PERCENTILE

Inverse function

PERCENTRANK finds rank, PERCENTILE finds value

Interpolation

Interpolates when needed

PERCENTRANK interpolates rank

Function Anatomy

=PERCENTRANK(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Percentile Ranking

Find percentile rank of values

Relative Position

Compare values to data set

Inverse Percentile

Find rank instead of value

Data Analysis

Understand value position

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=PERCENTRANK(array, x, significance)

Required

array:

Array or range of data values.

Required

The value for which you want to find the percentile rank.

Optional

significance:

Number of significant digits (default 3).

Returns

Return Value:

The percentile rank (0 to 1)

Description: Returns the percentile rank of a value

Interactive Examples

Basic PERCENTRANK

Find percentile rank of a value

Input Text:

"A1:A10, 50"

Formula:

=PERCENTRANK(A1:A10, 50)

Result:

Percentile rank (0 to 1)

Returns the percentile rank of 50 in the data set. 0.5 means 50th percentile (median).

VBA Implementation & Automation

Basic PERCENTRANK in VBA

Use PERCENTRANK function in VBA

' Basic PERCENTRANK in VBA
Range("C1").Value = Application.WorksheetFunction.PercentRank(Range("A1:A10"), 50)
' Returns: Percentile rank (0 to 1)

' Calculate percentile rank
Sub CalculatePercentRank()
    Dim percentRankValue As Double
    Dim testValue As Double
    testValue = 75 ' Value to find rank
    percentRankValue = Application.WorksheetFunction.PercentRank(Range("A1:A10"), testValue)
    Range("B1").Value = percentRankValue
End Sub

' Compare PERCENTRANK with PERCENTRANK.INC
Sub ComparePercentRankINC()
    Dim percentRankValue As Double
    Dim percentRankINCValue As Double
    Dim testValue As Double
    
    testValue = 75
    percentRankValue = Application.WorksheetFunction.PercentRank(Range("A1:A10"), testValue)
    percentRankINCValue = Application.WorksheetFunction.PercentRank_Inc(Range("A1:A10"), testValue)
    
    Range("B1").Value = "PERCENTRANK: " & percentRankValue
    Range("B2").Value = "PERCENTRANK.INC: " & percentRankINCValue
    ' Should be equal
End Sub

' Use significance parameter
Sub PercentRankWithSignificance()
    Dim percentRankValue As Double
    Dim testValue As Double
    
    testValue = 75
    percentRankValue = Application.WorksheetFunction.PercentRank(Range("A1:A10"), testValue, 2)
    Range("B1").Value = percentRankValue ' Rounded to 2 decimals
End Sub

' Find ranks for multiple values
Sub FindMultipleRanks()
    Dim values As Variant
    Dim i As Integer
    Dim rankValue As Double
    
    values = Array(50, 60, 70, 80, 90)
    
    For i = 0 To UBound(values)
        rankValue = Application.WorksheetFunction.PercentRank(Range("A1:A10"), values(i))
        Range("B" & (i + 1)).Value = values(i) & ": " & rankValue
    Next i
End Sub

Business Applications

Percentile Ranking

Find percentile rank of values

=PERCENTRANK(data_range, value)

Relative Position

Compare values to data set

=PERCENTRANK(range, test_value)

Inverse Percentile

Find rank instead of value

=PERCENTRANK(array, x)

Data Analysis

Understand value position

=PERCENTRANK(values, value, 2)

Common Issues & Solutions

#N/A Error

PERCENTRANK returns #N/A

x must be numeric and comparable to array values

Solution: x must be a numeric value that can be compared to array values. If x is text or cannot be compared, PERCENTRANK returns #N/A. Ensure x is numeric.

PERCENTRANK vs PERCENTRANK.INC

Uncertainty about difference

PERCENTRANK = PERCENTRANK.INC (identical functions)

Solution: PERCENTRANK and PERCENTRANK.INC are equivalent - they have identical behavior. Both use inclusive method. Use either one.

PERCENTRANK vs PERCENTILE

Uncertainty about difference

PERCENTRANK: value → rank, PERCENTILE: rank → value

Solution: PERCENTRANK finds the percentile rank of a value (value → rank). PERCENTILE finds the value at a percentile (rank → value). They are inverse functions.