How to Use the QUARTILE.INC and QUARTILE.EXC Functions in Microsoft Excel for Quartile Analysis

When analysing numerical datasets—such as employee salary structures, student examination results, customer transaction sizes, or quality assurance test scores—relying strictly on arithmetic averages (means) can provide a misleading picture due to extreme outliers. In statistical exploratory analysis, quartiles divide an ordered dataset into four equal segments, representing 25% increments across your sample distribution. In Microsoft Excel, calculating quartiles is performed using two specialised statistical functions: QUARTILE.INC (inclusive) and QUARTILE.EXC (exclusive). Knowing when and how to deploy each function ensures accurate descriptive statistics and robust box-and-whisker data models.

QUARTILE.INC vs QUARTILE.EXC: Key Differences

Both functions compute quartile cutoffs, but they employ differing statistical percentile boundaries:

• QUARTILE.INC (Inclusive): Calculates quartiles based on a percentile range of 0 to 1 (inclusive). It includes the minimum (0th percentile) and maximum (100th percentile) values of the dataset as valid quartiles. This matches the legacy QUARTILE function and standard box plot conventions in business reporting.
• QUARTILE.EXC (Exclusive): Calculates quartiles based on a percentile range of 0 < p < 1 (exclusive). It excludes the absolute minimum and maximum data points from quartile boundaries. This is commonly preferred in rigorous academic and inferential statistics to avoid extreme sample boundary biases.

Syntax and Parameters for QUARTILE.INC and QUARTILE.EXC

Both functions share identical two-argument syntax:

=QUARTILE.INC(array, quart)
=QUARTILE.EXC(array, quart)

• array: The range of cells or numeric array containing your data points.
• quart: A whole integer indicating which quartile cutoff value you wish to calculate:
  • 0: Minimum value (valid for QUARTILE.INC only).
  • 1: First Quartile (25th percentile, Q1) — 25% of data falls below this threshold.
  • 2: Median value (50th percentile, Q2) — splits the distribution in half.
  • 3: Third Quartile (75th percentile, Q3) — 75% of data falls below this threshold.
  • 4: Maximum value (valid for QUARTILE.INC only).

Step-by-Step Example: Calculating Quartiles and Interquartile Range (IQR)

Consider a quality control dataset of 12 test batch durability scores recorded in cells A2:A13:

45, 52, 58, 63, 67, 71, 74, 79, 82, 88, 91, 96

Step 1: Calculate the First Quartile (Q1)

To determine the 25th percentile cutoff using the inclusive method, enter the following formula in cell C2:

=QUARTILE.INC(A2:A13, 1)

Excel evaluates the lower quartile, returning 61.75. This indicates that 25% of all tested batches scored 61.75 or lower.

Step 2: Calculate the Median (Q2)

To find the 50th percentile midpoint, enter this formula in cell C3:

=QUARTILE.INC(A2:A13, 2)

Excel returns 72.5. (This yields the identical result as using =MEDIAN(A2:A13)).

Step 3: Calculate the Third Quartile (Q3)

To determine the 75th percentile cutoff, enter this formula in cell C4:

=QUARTILE.INC(A2:A13, 3)

Excel returns 80.5. This means that 75% of durability scores fell at or below 80.5 (or conversely, the top 25% scored higher).

Step 4: Calculate the Interquartile Range (IQR) for Outlier Detection

The Interquartile Range represents the middle 50% spread of your data, serving as the foundation for identifying statistical anomalies:

=C4 - C2

In this example, 80.5 - 61.75 = 18.75. Using standard Tukey outlier fences, any data point falling beyond Q1 - (1.5 * IQR) or above Q3 + (1.5 * IQR) can be flagged as an outlier.

Using QUARTILE.EXC for Exclusive Distribution Analysis

When conducting academic research where the population distribution is modeled continuously without assuming sample extremities equal the population limits, use QUARTILE.EXC:

=QUARTILE.EXC(A2:A13, 1)
=QUARTILE.EXC(A2:A13, 3)

In our sample dataset, QUARTILE.EXC returns 59.25 for Q1 and 86.5 for Q3. Notice that exclusive quartiles push boundaries further toward the interior of the dataset.

Troubleshooting Common Errors (#NUM!, #VALUE!)

When working with quartile formulas in Excel, look out for these common calculation pitfalls:

• #NUM! Error in QUARTILE.EXC: Occurs if quart is set to 0 or 4, since exclusive calculation excludes min/max percentiles. It also occurs if your dataset contains too few data points (QUARTILE.EXC requires a minimum of quart / (N + 1) validity, typically needing at least 4 observations for Q1 and Q3).
• #NUM! Error in QUARTILE.INC: Occurs if the quart argument is less than 0 or greater than 4.
• #VALUE! Error: Occurs if the quart parameter is non-numeric (such as text strings).