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:
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?