PERCENTILE.EXC function

The PERCENTILE.EXC function in Excel is used to calculate the k-th percentile of a data set, excluding the 0th and 100th percentiles. It interpolates between values in the data set to determine the percentile, making it useful when you want to exclude the extreme ends of the distribution.

Syntax:

PERCENTILE.EXC(array, k)

Arguments:

• array: Required. The array or range of data for which you want to calculate the percentile. The data should be numerical and can include numbers, cell references, or a range of values.
• k: Required. The percentile value, expressed as a decimal, between 0 and 1. For example:
  • 0.25 for the 25th percentile,
  • 0.5 for the 50th percentile (median),
  • 0.95 for the 95th percentile.

  The value of k must be between 0 and 1, exclusive (i.e., 0 < k < 1).

Output:

The function returns the k-th percentile in the data set, which is the value below which a given percentage of the data falls.

How It Works:

• PERCENTILE.EXC calculates the k-th percentile by excluding the 0th and 100th percentiles. This means it interpolates between the data values to determine the desired percentile.
• The k-th percentile corresponds to the value that has k × 100% of the data points less than or equal to it. For example, the 25th percentile is the value below which 25% of the data falls.

Example 1: Finding the 25th Percentile

Suppose you have a data set in cells A1:A10:

5, 10, 15, 20, 25, 30, 35, 40, 45, 50

To find the 25th percentile (25% of the data), use the formula:

=PERCENTILE.EXC(A1:A10, 0.25)

This will return the value that separates the lowest 25% of the data from the rest.

Example 2: Finding the 50th Percentile (Median)

To find the 50th percentile (the median) of the same data set, use the formula:

=PERCENTILE.EXC(A1:A10, 0.5)

This will return the median value, which is the midpoint of the data set.

Example 3: Finding the 90th Percentile

To find the 90th percentile of the data set, use the formula:

=PERCENTILE.EXC(A1:A10, 0.9)

This will return the value below which 90% of the data falls.

Key Points:

• PERCENTILE.EXC excludes the 0th and 100th percentiles, meaning it calculates percentiles only for the range between the 1st and 99th percentiles.
• The function is used when you want a percentile value but need to exclude the extreme ends (0th and 100th).
• The value of k must be between 0 and 1 (exclusive), and the data should be numerical.

Use Cases:

• Statistics: Calculate percentiles for data distributions, excluding extreme values.
• Risk Management: Determine thresholds for extreme values in financial or environmental data (e.g., 95th percentile for risk assessment).
• Quality Control: Identify data points falling within certain percentiles, such as the top 10% or bottom 25% of production values.

Notes:

• PERCENTILE.EXC will return an error if k is less than 0 or greater than 1, or if the data range is empty.
• If you need to include the 0th and 100th percentiles (i.e., the extreme values), use PERCENTILE.INC instead.