PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel

The following article introduces you to the PERCENTILE.EXC function - one of the functions in the statistical function group is very popular in Excel.

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 1

Description: The function returns the kth percentile of values ​​in the range with k in the range from 0 to 1, excluding values ​​0 and 1.

Syntax: PERCENTILE.EXC (array, k)

Inside:

- array: An array or range of data to determine percentile, which is a required parameter .

- k: Percentage value in the range from 0 to 1 including 0 and 1, is a required parameter .

Attention:

- If array array value is empty -> the function returns the #NUM! Error value .

- If the value of k is not a number -> the function returns the error value #VALUE! .

- If k ≤ 0 or k ≥ 1 -> the function returns the #NUM! Error value

- If k is not a multiple of 1 / (n - 1) -> interpolation function to determine the kth percentile value.

- PERCENTILE.EXC function will interpolate when the percentile return value is between two values ​​in the array. Where the function cannot be interpolated -> the function returns the #NUM! Error value

For example:

Find the value of 0.56 percentile in the dataset in the data table below:

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 2

- In the cell to calculate enter the formula : = PERCENTILE.EXC (E6: K6,3)

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 3

- Press Enter -> 0.56 percentile value in the dataset in the dataset is:

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 4

- Where the percentile value k = 0 -> the function returns the #NUM! Error value

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 5

- Where the percentile value k> 1 -> the function returns the #NUM! Error value

PERCENTILE.EXC function - The function returns the kth percentile of values ​​in the range, with k in the range from 0 to 1 in Excel Picture 6

Above are instructions and some specific examples when using PERCENTILE.EXC function in Excel.

Good luck!