Difference between revisions of "Documentation/How Tos/Calc: PERCENTILE function"

Latest revision as of 14:53, 2 February 2024

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 an 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.

Issues:

• Despite its name, PERCENTILE requires a fraction, not a percentage.

• QUARTILE
• PERCENTRANK
• RANK
• MEDIAN
• MAX
• MIN

• Statistical functions

• Functions listed alphabetically
• Functions listed by category