Returns a specified percentile in a list of numbers.

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.

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.

PRODUCT & FEATURES

CAPTCHA Protection

RESOURCES

© 2023 Zapof