QUARTILE.EXC vs. QUARTILE.INC in Excel: What’s the Difference?


Quartiles are values that split up a dataset into four equal parts.

There are three different functions you can use to calculate quartiles in Excel:

1. QUARTILE.EXC: This function uses the following process to calculate the quartiles of a dataset:

  • Use the median to separate the dataset into two halves.
  • Calculate Q1 as the median value in the lower half and Q3 as the median value in the upper half. Be sure to exclude the median of the dataset when calculating Q1 and Q3.

2. QUARTILE.INC: This function uses the following process to calculate the quartiles of a dataset:

  • Use the median to separate the dataset into two halves.
  • Calculate Q1 as the median value in the lower half and Q3 as the median value in the upper half. Be sure to include the median of the dataset when calculating Q1 and Q3.

3. QUARTILE: This function calculates the quartiles of a dataset as well. It will return the exact same value as the QUARTILE.INC function.

For example, suppose we have the following dataset:

Dataset: 4, 6, 6, 7, 8, 12, 15, 17, 20, 21, 21, 23, 24, 27, 28

The QUARTILE.EXC function will use the median to separate the dataset into two halves and calculate Q1 and Q3 as 7 and 23, respectively:

  • Q1: Median of 4, 6, 6, 7, 8, 12, 15 = 7
  • Q3: Median of 20, 21, 21, 23, 24, 27, 28 = 23

The QUARTILE.INC function will use the median to separate the dataset into two halves and calculate Q1 and Q3 as 7.5 and 22, respectively:

  • Q1: Median of 4, 6, 6, 7, 8, 12, 15, 17 = 7.5
  • Q3: Median of 17, 20, 21, 21, 23, 24, 27, 28 = 22

The following example shows how to use the various QUARTILE functions in Excel.

Example: QUARTILE.EXC vs. QUARTILE.INC in Excel

Suppose we have the following dataset in Excel:

The following screenshot shows how to calculate the quartiles for the dataset using the three different quartile formulas:

QUARTILE.EXC vs. QUARTILE.INC in Excel

Using the QUARTILE or QUARTILE.INC functions, we calculate the lower and upper quartiles as:

  • Q1: 7.5
  • Q3: 22

Conversely, using the QUARTILE.EXC function we calculate the lower and upper quartiles as:

  • Q1: 7
  • Q3: 23

When to Use QUARTILE.EXC vs. QUARTILE.INC

There is no universally “correct” way to calculate the quartiles in a dataset.

In fact, different statistical softwares use different default formulas to calculate quartiles.

The R programming language uses a formula that matches the QUARTILE.INC function in Excel.

The Python programming language uses a formula that matches the QUARTILE.INC function in Excel.

TI-84 calculators use a formula that matches the QUARTILE.EXC function in Excel.

Fortunately, no matter which function you use to calculate quartiles the difference between the values calculated by QUARTILE.INC and QUARTILE.EXC will be very similar in most cases.

In some cases, it’s even possible that the two functions will return the same values depending on the sequence of numbers in the dataset.

Additional Resources

STDEV.P vs. STDEV.S in Excel: What’s the Difference?
VAR.P vs. VAR.S in Excel: What’s the Difference?

4 Replies to “QUARTILE.EXC vs. QUARTILE.INC in Excel: What’s the Difference?”

  1. The explanation above is not right.
    Consider the following 8 numbers: 1, 2, 4, 5, 7, 8, 10, 11.
    According to the rules above, the first quartile should be the median of 1, 2, 4, and 5, which is 3. However, QUARTILE.EXC and QUARTILE.INC yield 2.5 and 3.5, respectively.

  2. The TI calculator does not use the same method as the =QUARTILE.EXC nor the .INC. The difference is that the TI calculator will find the median of the lower and upper halves, excluding the median. But Excel does a weighted median of the lower and upper halves. Try for a sample size of 14 and you will see that Q1 and Q3 will be 25% of the way between the 4th and 5th values in Excel, but the TI would use 50% of the way between the 4th and 5th values.

  3. You said that quartile.exc calculates the same as a ti calculator but it won’t for me. In fact, I can’t get either exc or inc to get a q3 that makes sense to anyone I know that teaches statistics for the following data set. All technologies and methods that I can find will give me q3 as 42.5 but excel gives me 41.5 and 53.5 for inc and exc respectively. Any help on why I am having this issue would be appreciated.
    5
    10
    10
    15
    15
    15
    15
    20
    20
    20
    25
    30
    30
    40
    40
    45
    60
    60
    65
    85

  4. Orange Data Mining software appears to be calculating quartiles that represent the average of quartile.exc and quartile.inc values. Is this mathematically valid?

Leave a Reply

Your email address will not be published. Required fields are marked *