How to Use the PERCENTILE.INC and PERCENTILE.EXC Functions in Microsoft Excel

Statistical distribution analysis in Microsoft Excel frequently requires calculating percentiles to measure where individual data points rank across a wider dataset. Excel provides two primary functions for this purpose: PERCENTILE.INC (inclusive) and PERCENTILE.EXC (exclusive). Introduced to replace the legacy PERCENTILE function, these tools calculate fractional rankings differently, which can produce meaningfully distinct statistical outputs depending on how dataset boundaries are handled.

Understanding the mathematical logic behind both functions ensures analysts, educators, and financial professionals apply the correct ranking models to survey evaluations, standardised test scores, and performance benchmarks.

PERCENTILE.INC vs PERCENTILE.EXC: Core Differences Explained

The distinction between the two functions lies in the acceptable range of the percentile argument k and the underlying interpolation calculation:

• PERCENTILE.INC (Inclusive): Calculates the k-th percentile where k can range anywhere from 0 to 1 (representing 0% to 100%). When k = 0, the function returns the minimum value of the dataset; when k = 1, it returns the maximum value.
• PERCENTILE.EXC (Exclusive): Calculates the k-th percentile where k must be strictly greater than 1 / (N + 1) and less than N / (N + 1), where N represents the total number of items in the dataset. Because the boundaries are excluded, passing 0 or 1 generates a #NUM! error.

PERCENTILE.INC is standard for general reporting, business metrics, and percentile ranks where 0th and 100th percentiles must correspond to dataset minimums and maximums. PERCENTILE.EXC is preferred in rigorous academic and statistical environments where sample boundaries do not accurately represent population extremes.

Syntax and Parameter Requirements

Both functions follow identical syntax rules:

=PERCENTILE.INC(array, k)
=PERCENTILE.EXC(array, k)

• array: The range or array of numeric values to evaluate (such as A2:A50). Text, blank cells, and logical values within the range are ignored automatically.
• k: The percentile value expressed either as a decimal between 0 and 1 or as a percentage (e.g., 0.75 or 75%).

Step-by-Step Implementation in Microsoft Excel

Step 1: Preparing and Structuring Sample Data

Assume you have an employee performance assessment dataset located in cells B2:B21 containing 20 test scores ranging between 55 and 98.

Step 2: Calculating Inclusive Percentiles

1. Select cell D2 where you wish to calculate the 90th percentile.
2. Enter the formula:

   =PERCENTILE.INC(B2:B21, 0.9)

3. Press Enter. Excel evaluates the 90th percentile threshold, interpolating between the nearest rank ranks if an exact match is not found.
4. To compute the median using percentiles, enter =PERCENTILE.INC(B2:B21, 0.5), which returns an identical result to =MEDIAN(B2:B21).

Step 3: Calculating Exclusive Percentiles

1. Select cell E2.
2. Enter the formula:

   =PERCENTILE.EXC(B2:B21, 0.9)

3. Press Enter. Because exclusive calculation divides ranks by N + 1 (here, 21), the interpolated 90th percentile score will often be slightly higher than the result produced by PERCENTILE.INC.

Handling Common Errors in Percentile Formulas

When working with percentile functions in Excel, specific errors may appear if input arguments violate statistical constraints:

• #NUM! Error: Occurs when the value of k is outside the valid range (negative numbers, numbers greater than 1, or values outside (1/(N+1), N/(N+1)) for PERCENTILE.EXC), or if the supplied array is empty.
• #VALUE! Error: Occurs if the argument supplied for k is non-numeric, or if formula syntax contains invalid characters.

Dynamic Percentile Tables with Sequence and Spill Arrays

In modern Excel (Microsoft 365 and Excel 2021+), dynamic array formulas allow generating an entire percentile breakdown across an array without dragging formulas down manually:

=PERCENTILE.INC(B2:B21, {0.1; 0.25; 0.5; 0.75; 0.9})

Entering this formula in a single cell spills the 10th, 25th, 50th, 75th, and 90th percentile boundaries down adjacent rows automatically, providing an instant five-number summary for data distribution analysis.