*96*

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