Documentation/How Tos/Calc: PERCENTILE function

From Apache OpenOffice Wiki
Jump to: navigation, search


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.

Issues:

  • Despite its name, PERCENTILE requires a fraction, not a percentage.
Template:Documentation/SeeAlso
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_PERCENTILE_function&oldid=225939"
Views
Personal tools
Navigation
Tools