Difference between revisions of "Documentation/How Tos/Calc: PERCENTILE function"
From Apache OpenOffice Wiki
< Documentation | How Tos
OOoWikiBot (Talk | contribs) m (Robot: Automated text replacement %s) |
m |
||
| Line 43: | Line 43: | ||
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] | ||
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | ||
| − | [[Category: Documentation/Reference/Calc]] | + | [[Category: Documentation/Reference/Calc/Statistical functions]] |
Revision as of 12:49, 17 May 2010
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.
