Description

This function returns the pth percentile from a list of values. In other words, it returns the value with a given percentile from a distribution.

Type

NUMERIC

Arguments

Type

Meaning

1

LIST

A list of NUMERIC values representing the distribution.

2

NUMERIC

The probability p for which you wish the corresponding value. Note that p should be between 0 and 1 inclusive.

Evaluation

The list in argument one describes the distribution by providing independently sampled values from that distribution. The function returns an estimate of the value which has the given probability p of being greater than a value taken from the distribution. Consequently, for an input probability p at most (100*p)% of the values in the data set will be less than the return value (and at most 100*(1-p)% will be greater than this value).

Several methods exist for computing the percentile; the following is the technical definition used by the Percentile function: for the pth percentile, Percentile(list, p):

Compute where N is the number of items in the data set. Then set k and d, where k + d = , k is an integer, and d is a fraction greater than or equal to 0 and less than 1. Essentially k is the integer part and d is the decimal part of .

Then, sort the numeric values in the list in increasing order. The function Y[i] denotes the i'th sorted value of the numeric list, where i is between 1 and N, inclusive.

Then:

If k = 0, Percentile(list, p) = Y[1]

If 0 < k < N, Percentile(list, p) = Y[k] + (d)(Y[k+1] - Y[k])

If k = N, Percentile(list, p) = Y[N]

For more details, refer to “NIST/SEMATECH e-Handbook of Statistical Methods” (http://www.itl.nist.gov/div898/handbook/prc/section2/prc252.htm).

Comments

Note that this function is sometimes called the “quantile” function.

Excel’s, Percentile function sets 1+p(N-1) equal to k + d, then proceeds as above. The two methods give similar results.