Formulas and functions > Functions from A to Z > PERCENTRANK (percent rank in a data set)

Note: PERCENTRANK is supplemented by the new identical function PERCENTRANK.INC and the completely new function PERCENTRANK.EXC, which are available in newer versions of Microsoft Excel (2010 or later).

Syntax:

PERCENTRANK(Range, Number [, Digits])

Description:

This returns the percentile rank (between 0 and 1 inclusive) of a value in a data set.

Range is a range with numeric data – usually a reference to a cell range in which numbers are entered.

Number is the value whose rank is to be determined. If Number is not included in the given values, interpolation will be used.

Digits (optional) is used to determine to how many decimal places the result should be calculated. For this purpose, enter the desired number of decimal places. If omitted, 3 digits will be used.

Example:

If A1:A9 contain the values 1, 2, 3, 4, 5, 6, 7, 8, 9, the following applies:

PERCENTRANK(A1:A9, 5) returns 0.5 (50%) middle of the given values; median

PERCENTRANK(A1:A9, 1) returns 0 (0%) minimum value

PERCENTRANK(A1:A9, 9) returns 1 (100%) maximum value