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

Syntax:

PERCENTRANK.EXC(Range, Number [, Digits])

Description:

Returns the percentile rank (between 0 and 1 exclusive) 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 you want to determine. If Number is not included in the given values, interpolation will be used.

Digits (optional) is the number of significant digits for the returned value. To do this, 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:

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

PERCENTRANK.EXC(A1:A9, 1) returns 0.1 (=10%) minimum value

PERCENTRANK.EXC(A1:A9, 9) returns 0.9 (=90%) maximum value

Note:

The PERCENTRANK.EXC function supplements together with PERCENTRANK.INC the previous PERCENTRANK function.

PERCENTRANK.INC returns the same result as PERCENTRANK (values 0 to 1 inclusive).

PERCENTRANK.EXC (values 0 to 1 exclusive) is completely new.

Compatibility notes:

Microsoft Excel supports this function only in version 2010 or later. In older versions, the function is unknown.