PERCENTILE.INC function
Array
The array from which to derive the result.
K
The percentile. Must be between 0 and 1 (inclusive), which can also be written as 0% and 100%.
Returns
The number of the given array at the kth percentile, or a linearly interpolated value if the number at the kth percentile falls between two numbers.
Returns the number of the given array at the kth percentile, where k is a number between 0 and 1 (inclusive), which can also be specified with the % operator (.5 equals 50%). PERCENTILE.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 50%)PERCENTILE.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 50%) returns 5 (known as the median, which can also be returned by the MEDIAN function).
This formula returns 3:
This formula returns 0:
This formula returns 10:
This formula specifies a number, 55%, which is not part of the array:
In the formula above, the second parameter 55%, falls between 5
and 6 in the array, which causes PERCENTILE.INC to use interpolation and
return 5.5 (a value which is not found in the array, but is an amalgamation
of the two values 5 and 6).
If k is set to 0 or 0%, the first element of the array is returned.
If k is set to 1 or 100%, the last element of the array is returned.
To exclude percentiles below 1 / (n + 1) and above n / (n
+ 1) (where n is the number of elements of the array), use
PERCENTILE.EXC instead. Both
functions map to the full range of the given array elements.
Related functions
Use PERCENTRANK.INC to return the percentile rank of a number, which can be seen as the reverse operation to PERCENTILE.INC. PERCENTILE.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 0.5)PERCENTILE.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 0,5) returns 5, and PERCENTRANK.INC({ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, 5)PERCENTRANK.INC({ 0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }; 5) returns .5.
QUARTILE.INC can be seen as a more limited version of this function, which divides the area into four parts (quarters).
These formulas both return the array { 1, 3, 5, 7, 9 }{ 1; 3; 5; 7; 9 }:
Note that the array elements range from 1 through 9, not 0 through 10.
(The second parameter to both functions can be an array, in which case the corresponding functions are evaluated once per array element and the results are collected in another array.)
Examples
Returns 5.
Returns .5 (50%). PERCENTRANK.INC can be seen as the reverse operation to PERCENTILE.INC.
Returns 3.
Returns 0.
Returns 10.
Returns 5.5, which is a linearly interpolated value, halfway between the values 5 and 6 which are found in the array.