The PERCENTILE.EXC function in Google Sheets is a powerful tool designed to determine the value at a specific percentile within a dataset. This function is particularly useful for statistical analysis, allowing users to focus on segments of data while avoiding the extreme boundaries.

Syntax

PERCENTILE.EXC(data, percentile)

data: The range of cells or array containing the dataset from which the percentile will be computed.
percentile: A number between 0 and 1, representing the percentile for which you want to find the value. Note that values 0 and 1 are excluded.

Example #1

=PERCENTILE.EXC(A1:A10, 0.75)

This function call computes the 75th percentile of the values in the range A1 to A10. For example, if A1:A10 contains the numbers 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100, the result would be 75.

Example #2

=PERCENTILE.EXC(B1:B20, 0.5)

Here, this function determines the 50th percentile, also known as the median, of the values in the range B1 to B20. If the values are 1, 3, 5, 7, …, 39, the median would be 20.

Example #3

=PERCENTILE.EXC(C1:C15, 0.9)

This example calculates the 90th percentile for the data located in cells C1 to C15. For instance, if the data consists of the numbers 2, 3, 5, 9, …, 100, the result might be 91.

Error handling

• NUM!: Indicates that the percentile is less than 0 or greater than 1. Make sure the percentile value is within the correct range.
• N/A: This error occurs if there are not enough data points. Ensure that the dataset provided is of sufficient size to calculate the requested percentile.
• REF!: Appears if the range specified for the dataset is not valid. Double-check that the cell references are correct and accessible.

Conclusion

The PERCENTILE.EXC function is invaluable for analyzing data distributions by providing a way to calculate the value at any specified percentile within a dataset, excluding extreme values. This can help in making informed decisions based on statistical analysis and understanding data trends more effectively.