Author: Oscar Cronquist Article last updated on November 16, 2018

The PERCENTRANK function calculates the percent rank of a given number compared to the whole data set. The image above shows 15 numbers in column B. 2 numbers are less than 11 (second argument) and 12 numbers are larger than 11.

2/(2+12) = 0.142 (approx 3 digits)

This function is in the compatibility category and has been replaced with the PERCENTRANK.EXC function and PERCENTRANK.INC function which was introduced in Excel 2010.

Formula in cell C7:

=PERCENTRANK(B3:B17,11)

Excel Function Syntax

PERCENTRANK(arrayx[significance])

Arguments

array Required. A parameter which determines the shape of the distribution.
x Required. A parameter which determines the shape of the distribution.
[significance] Optional. Lower bound, default value 0 (zero).

Comments

The PERCENTRANK function returns

  • #NUM! error value if:
    • array is empty
    • [significance] < 1

The PERCENTRANK function interpolates in order to return the correct value if x doesn't match a value in the array.