Documentation/Calc Functions/PERCENTILE.EXC

PERCENTILE.EXC

Category:

Statistical Analysis

Summary:

Calculates the alpha-percentile of data values in an array of numbers. Percentiles can be used to understand the significance of particular values within a data set. The nth percentile of a data set is the value below which n% of the data set lies. This function uses an alpha argument lying in the range 0 to 1 (exclusive), which corresponds to a percentage in the range 0% to 100% (exclusive). For example, an alpha of 0.5 gives the 50th percentile, also known as the median.

Syntax:

PERCENTILE.EXC(Data; Alpha)

Returns:

Returns a real number which is the scale value for the specified data set and alpha.

Arguments:

Data is an array or a reference to the range of cells that represents the array of data.

Alpha is a real number or a reference to the cell containing that number which represents the percentage of the scale between 0 and 1. Values 0 and 1 are excluded.

• If Alpha is greater than or equal to 1 or less than or equal to 0 then the function returns an error value.
• If Alpha is not a multiple of 1/(n+1), (where n is the number of values in the supplied array), the function interpolates between the values in the supplied array, to calculate the percentile value. However, if Alpha is less than 1/(n+1) or Alpha is greater than n/(n+1), the function is unable to interpolate, and so returns an error.

• Alpha = 0.25 gives the first quartile, Alpha = 0.5 gives the median, and Alpha = 0.75 gives the third quartile.
• For more details on percentiles, visit Wikipedia.
 The difference between PERCENTILE.INC/PERCENTILE and PERCENTILE.EXC is that, in the PERCENTILE.INC function the value of alpha is within the range 0 to 1 inclusive, and in the PERCENTILE.EXC function, the value of alpha is within the range 0 to 1 exclusive.

Examples:

Formula Description Returns
=PERCENTILE.EXC(A1:A5;0.4) where A1:A5 contains 5,7,3,2,9 The function calculates the value in the data set, which equals 40% of the total data scale in A1:A5. Here Alpha is not a multiple of 1/(5+1) so interpolation occurs. 3.8
=PERCENTILE.EXC(A1:A5;0.16) where A1:A5 contains 5,7,3,2,9 The Alpha value must be greater than 1/(5+1) or 0.16666... and less than 5/(5+1) or 0.83333... which in this case is not followed hence the function returns an error value Err:504
=PERCENTILE.EXC(A1:A5;1) where A1:A5 contains 5,7,3,2,9 The function returns an error value since Alpha is not in the range 0 to 1, exclusive. Err:502
=PERCENTILE.EXC({5;7;3;2;9};0.1) The function returns an error value because value of Alpha is less than 1/(5+1) = 1.66... Err:504
=PERCENTILE.EXC({5;7;3;2;9},5/6) The function calculates the value in the data set, which equals 5/6th of the total data scale. Here Alpha is a multiple of 1/(5+1) so no interpolation occurs returning an exact value from the data set. 9

None

PERCENTILE.EXC