PERCENTILE.INC function

The PERCENTILE.INC function in Excel is used to calculate the k-th percentile of a data set, including the 0th and 100th percentiles. This means it includes the extreme values of the data range when calculating percentiles.

Syntax:

PERCENTILE.INC(array, k)

Arguments:

• array: Required. The array or range of data for which you want to calculate the percentile. This data must be numeric.
• 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 can range from 0 to 1 (inclusive), unlike PERCENTILE.EXC, which excludes the 0th and 100th percentiles.

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. It includes both the 0th and 100th percentiles, meaning it calculates percentiles for the full data range.

How It Works:

• PERCENTILE.INC calculates the k-th percentile by including all values in the range, from the minimum (0th percentile) to the maximum (100th 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.INC(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.INC(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.INC(A1:A10, 0.9)

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

Key Points:

• PERCENTILE.INC includes the 0th and 100th percentiles, meaning it calculates percentiles for the entire data range.
• The function is used when you want to include the extreme values (0th and 100th percentiles) in the calculation.
• The value of k can range from 0 to 1 (inclusive), and the data should be numerical.

Use Cases:

• Statistics: Calculate percentiles for data distributions, including the full range of 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.INC will return an error if k is less than 0 or greater than 1, or if the data range is empty.
• If you want to exclude the 0th and 100th percentiles (i.e., the extreme values), use PERCENTILE.EXC instead.