PERCENTILE


Returns a specified percentile in a list of numbers.

Syntax:

PERCENTILE(numberlist, fraction)

PERCENTILE returns the value below which fraction of the numbers in numberlist lie. fraction must be between 0 and 1 inclusive; 
numberlist is a range or array of numbers, not necessarily in order.
PERCENTILE internally assigns a rank to each number in numberlist, where 0 is the rank of the lowest number, 1 of the next lowest and so on. The rank r of the value to be found is fraction * (N-1), where N is the count of numbers in numberlist. If r is integer the corresponding value from numberlist is returned; otherwise the value is calculated proportionately between the values with rank |r| and |r|+1 (see the examples).
This function may be useful for example when determining a score below which lies a certain percentage of all scores for a test. It is of limited use with a small list of numbers.
PERCENTILE returns the maximum, median, minimum value when fraction is 1, 0.5, 0 respectively.

Example:

PERCENTILE(A2:A99, 0.6)
where cells A2:A99 contain scores from a test, returns the score below which 60% of the scores lie.
The next two examples illustrate the calculation, but are not intended to show practical application:
PERCENTILE({3, 4, 9, 11, 12}, 0.5)
returns 9, the median value of the list.
PERCENTILE({9, 3, 7, 8}, 0.25)
returns 6. There are 4 numbers in the list. The rank of the value to be found is 0.25 * (4-1) = 0.75. The lowest number 3 is assigned rank 0; the next highest number 7 is assigned rank 1. The value with rank 0.75 lies proportionately between 3 and 7, and is calculated as 3 + (7-3)*0.75 = 6.







 
Created with Zapof