You run a quartile formula on the same dataset in two different tools and get different answers. Both are correct — they use different methods. Excel offers two quartile functions precisely because two legitimate statistical conventions exist: QUARTILE.INC uses percentile positions from 0 to 1 inclusive. QUARTILE.EXC uses positions from 0 to 1 exclusive, treating the endpoints as outside the percentile scale.
The difference matters when your results must match a specific tool, textbook, or reporting standard. R and Python default to the inclusive method. TI-84 calculators default to the exclusive method. Choosing the wrong function means your Excel quartiles will not match your colleagues’ analysis — even on the same raw dataset. This guide explains both functions fully and shows when to use each one.
What Are Quartiles?
Quartiles divide an ordered dataset into four equal parts. Q1 marks the 25th percentile — 25% of values fall below it. Q2 is the median at the 50th percentile, and Q3 marks the 75th percentile. Together, Q1 and Q3 define the interquartile range (IQR), which measures the spread of the middle 50% of the data.
Where Q1 and Q3 fall depends on whether the median is included or excluded when splitting the data into halves. This is the only difference between the two functions. For datasets with an even number of values, both methods often agree. For datasets with an odd number of values, the results frequently differ.
What Is the Syntax for Each Function?
Both functions share the same two-argument structure.
| Argument | Required? | What it does |
|---|---|---|
| array | Required | The range or array of numeric values to analyse. Text, logical values, and empty cells are ignored. At least one numeric value is required. |
| quart | Required | An integer indicating which quartile to return. QUARTILE.INC accepts 0 through 4. QUARTILE.EXC accepts only 1 through 3. |
How Does the Quart Argument Differ Between the Two Functions?
The quart argument is where the two functions diverge most visibly. QUARTILE.INC accepts quart values from 0 to 4 — including the minimum (0) and maximum (4). QUARTILE.EXC only accepts quart values from 1 to 3. Consequently, it cannot return the minimum or maximum because those are the excluded endpoints.
| quart | Returns | QUARTILE.INC | QUARTILE.EXC |
|---|---|---|---|
| 0 | Minimum | ✓ Returns MIN | ✗ #NUM! error |
| 1 | Q1 — 25th percentile | ✓ Valid | ✓ Valid |
| 2 | Q2 — Median | = MEDIAN() | = MEDIAN() |
| 3 | Q3 — 75th percentile | ✓ Valid | ✓ Valid |
| 4 | Maximum | ✓ Returns MAX | ✗ #NUM! error |
How Do the Results Compare on the Same Dataset?
Consider the nine values {1, 3, 5, 7, 9, 11, 13, 15, 17}. The median is 9. QUARTILE.INC includes 9 when calculating Q1 and Q3. QUARTILE.EXC excludes it. The table below shows the difference.
The pattern is consistent: QUARTILE.EXC produces a lower Q1 and a higher Q3 than QUARTILE.INC. Consequently, the exclusive method always yields a wider interquartile range. A wider IQR means the outlier fences are set further out — so fewer values are flagged as outliers.
Examples 1–4: Core Quartile Calculations
The starting point for any quartile analysis is calculating Q1 and Q3. Running both functions in adjacent columns makes the difference immediately visible. This lets you choose the appropriate result before the analysis goes further.
The IQR is Q3 minus Q1. It measures the spread of the central half of the dataset, unaffected by extreme values. Both functions produce a valid IQR — however, they produce different values from each other. The exclusive method produces a wider IQR. Pick one method and use it consistently throughout the analysis.
Outlier Detection and Five-Number Summary
The standard Tukey fence method defines outlier boundaries at Q1 − 1.5×IQR and Q3 + 1.5×IQR. Both functions work for this purpose. The exclusive method produces wider fences, so it flags fewer values as outliers. Always document which function you used so readers can verify the fence boundaries.
A five-number summary shows the minimum, Q1, median, Q3, and maximum. This is the data that drives a box plot. Only QUARTILE.INC can produce all five values from one function. QUARTILE.EXC cannot return the minimum (quart 0) or maximum (quart 4).
Examples 5–8: Real-World Scenarios
Matching Tools, Classifying Data, and the Decision Framework
R’s quantile() and Python’s numpy.quantile() both default to the inclusive interpolation method — the same method as QUARTILE.INC. Consequently, if your Excel analysis must match output from either language, QUARTILE.INC is the correct choice.
A common business task is labelling each data row with its quartile band — “Bottom 25%”, “Lower mid”, “Upper mid”, or “Top 25%”. Nested IF formulas using quartile thresholds achieve this. The same pattern works with either function — just store the thresholds in helper cells first.
The quart argument accepts a cell reference. This makes the formula dynamic — a dropdown or input cell controls which quartile displays without editing any formula. This pattern is specifically useful for dashboards where users select which statistic to view.
Most users should default to QUARTILE.INC. It matches the most common statistical tools and textbooks. Use QUARTILE.EXC only when a specific tool or standard requires it. Never mix both functions in the same analysis — inconsistency in the method will produce IQR values that cannot be compared.
Common QUARTILE Issues and How to Fix Them
#NUM! error from QUARTILE.EXC
QUARTILE.EXC returns #NUM! for two reasons: the quart argument is 0 or 4 (use MIN or MAX instead), or the array is empty. Additionally, if the array has only two values, QUARTILE.EXC(array, 1) returns #NUM! because the exclusive method requires at least three values to calculate Q1. Switch to QUARTILE.INC or add more data.
Results do not match R or Python
R and Python both default to the inclusive method — but each supports multiple alternative interpolation types. If your code specified a non-default method (for example, numpy’s method=‘hazen’ or type=5 in R), the result may not match either Excel function directly. Confirm which specific method your code uses before choosing between the two functions.
IQR differs from a colleague’s result on the same data
Two analysts using the same dataset but different functions will get different IQR values. This is expected and not an error in either analysis. The fix is to agree on one method before the analysis begins and document it in the report. For most business contexts, QUARTILE.INC is the conventional choice.
=QUARTILE(A2:A100, 1) becomes =QUARTILE.INC(A2:A100, 1) with no change to results.Frequently Asked Questions
-
What is the difference between QUARTILE.INC and QUARTILE.EXC?Both functions calculate quartiles but use different percentile conventions. QUARTILE.INC uses a range of 0 to 1 inclusive — the minimum and maximum are included. It accepts quart values from 0 to 4. QUARTILE.EXC uses a range of 0 to 1 exclusive — the endpoints are outside the scale. It only accepts quart values from 1 to 3. On the same dataset, QUARTILE.EXC produces a lower Q1 and higher Q3 than QUARTILE.INC, yielding a wider IQR. Both methods are statistically valid — the choice depends on which tool or standard you need to match.
-
Which quartile function should I use as my default?Use QUARTILE.INC as your default. It matches the most widely used statistical tools — R’s quantile(), Python’s numpy.quantile(), and the legacy Excel QUARTILE function. Use QUARTILE.EXC only when your results need to match a TI-84 calculator, or when a specific reporting standard requires the exclusive method. The most important rule is consistency: use one method throughout a single analysis and document which one you chose.
-
Why does QUARTILE.EXC return #NUM! for quart 0 or 4?The exclusive method defines the percentile range as strictly between 0 and 1 — the 0th percentile (minimum) and 100th percentile (maximum) are outside this range. Consequently, QUARTILE.EXC cannot calculate them by design. To get the minimum, use =MIN(array). To get the maximum, use =MAX(array). If you need all five values of a five-number summary from one function, use QUARTILE.INC with quart values 0 through 4.
More Questions About QUARTILE
-
Do QUARTILE.INC and QUARTILE.EXC always give different results?Not always. Both functions always return the same Q2 (median). For Q1 and Q3, they often differ — but for some datasets the interpolation produces identical values. The difference is most noticeable on small datasets with an odd number of values. For large datasets (n > 50), the practical difference is usually very small. You should still choose one method deliberately rather than assuming the results will match.
-
Does QUARTILE.INC match the legacy QUARTILE function?Yes, exactly. QUARTILE.INC and the legacy QUARTILE function use identical calculations and return identical results for every quart value. Microsoft introduced QUARTILE.INC to make the inclusive method explicit by name. Replace any QUARTILE with QUARTILE.INC in new workbooks — it is a direct swap with no change to results. Microsoft may remove QUARTILE from future Excel versions.
-
Can I use QUARTILE functions in Google Sheets?Yes. Google Sheets supports both QUARTILE.INC and QUARTILE.EXC with identical syntax and identical calculation methods. Results match perfectly between the two platforms. Google Sheets also supports the legacy QUARTILE function. As in Excel, QUARTILE.INC is the recommended function for new sheets.