Understanding Excel’s QUARTILE.EXC and QUARTILE.INC Functions

The QUARTILE(), QUARTILE.INC(), and QUARTILE.EXC are statistical functions in Excel that allow you to calculate quartiles in Excel. These functions are useful in calculating statistical measures such as the interquartile range. In this tutorial, you’ll learn about the difference between the QUARTILE.INC(), QUARTILE.EXC(), and QUARTILE() functions. Understanding the differences between these functions is an important thing to understand in order to ensure that your calculations work as expected.

By the end of this tutorial, you’ll have learned the following:

• What the differences between the Excel QUARTILE(), QUARTILE.INC(), and QUARTILE.EXC() functions are
• When to use which of the functions

What are Quartiles?

Quartiles are values that are used to divide your data into four fragments of equal number of observations. Quartiles use the concept of a median as a measure of central tendency. Similarly, when we divide a data into quartiles, data are split based on medians, rather than based on other measures of central tendency.

This is better explained using an example. In the example below, we have 13 numbers:

3, 5, 8, 9, 12, 13, 14, 18, 21, 23, 24, 28, 29

When calculating quadrants, we need to be mindful of whether or not we want to include the median of the dataset. This is where the many different functions in Excel come into play. When we use the median to calculate the quadrants, the quadrants can either include or exclude the median from calculating the quadrant.

Quadrant 2 will always be the median of the dataset. However, to calculate quadrants 1 or 3, we need to decide whether or not the median is included in finding the values for quadrants 1 and 3. In the following sections, you’ll learn how Excel uses three different functions to allow you to customize the way in which quadrants are calculated.

Understanding Excel’s Functions for Calculating Quartiles

Excel provides three different functions to calculate the quadrants of a dataset:

1. QUARTILE(),
2. QUARTILE.INC(), and
3. QUARTILE.EXC()

The QUARTILE() function provides the same calculation as the QUARTILE.INC() function. Let’s dive into how these functions work and how they’re different.

Understanding the QUARTILE() Function

The Excel QUARTILE() function uses the following process to calculate quartiles:

1. Quartile 2 is defined by using the median to separate the dataset in half
2. Quartile 1 is then defined as the median value of the lower half, where the median value is included. Quartile 3, inversely, is defined as the median value of the upper half, where the median value is included.

This is more easily explained with an example. Let’s take a look at a dataset with nine numbers:

1, 3, 4, 5, 8, 9, 10, 13, 14

Let’s see how we can calculate our quartiles:

1. Quartile 2 is the median of the dataset. In this case, that’s 8.
2. Quartile 1 is the median between the first value and the median value. In this case, that’s 4.
3. Quartile 3 is the median between the median value and the last value. In this case, that’s 10.

Take a look at the image below to see how these values can be calculated in Excel:

In the following section, you’ll learn how to use the QUARTILE.INC() function in Excel.

Understanding the QUARTILE.INC() Function

The Excel QUARTILE.INC() function works in the same way as the QUARTILE() function. In many ways, this implies that this is the default way of calculating the quadrants of a dataset.

The Excel QUARTILE.INC() function uses the following process to calculate quartiles:

1. Quartile 2 is defined by using the median to separate the dataset in half
2. Quartile 1 is then defined as the median value of the lower half, where the median value is included. Quartile 3, inversely, is defined as the median value of the upper half, where the median value is included.

This is more easily explained with an example. Let’s take a look at a dataset with nine numbers:

1, 3, 4, 5, 8, 9, 10, 13, 14

Let’s see how we can calculate our quartiles:

1. Quartile 2 is the median of the dataset. In this case, that’s 8.
2. Quartile 1 is the median between the first value and the median value. In this case, that’s 4.
3. Quartile 3 is the median between the median value and the last value. In this case, that’s 10.

Take a look at the image below to see how these values can be calculated in Excel using the QUARTILE.INC() function:

In the following section, you’ll learn how to understand how the Excel QUARTILE.EXC() function differs from the two functions above.

Understanding the QUARTILE.EXC() Function

The Excel QUARTILE.EXC() function differs from both the QUARTILE() and QUARTILE.INC() functions in that it excludes the median from calculating the first and third quadrants.

The Excel QUARTILE.EXC() function uses the following process to calculate quartiles:

1. Quartile 2 is defined by using the median to separate the dataset in half
2. Quartile 1 is then defined as the median value of the lower half, where the median value is excluded. Quartile 3, inversely, is defined as the median value of the upper half, where the median value is excluded.

This is more easily explained with an example. Let’s take a look at a dataset with nine numbers:

1, 3, 4, 5, 8, 9, 10, 13, 14

Let’s see how we can calculate our quartiles:

1. Quartile 2 is the median of the dataset. In this case, that’s 8.
2. Quartile 1 is the median between the first value and up to the median value. In this case, that’s 3.5.
3. Quartile 3 is the median between the median value (but not including) and the last value. In this case, that’s 11.5.

Take a look at the image below to see how these values can be calculated in Excel using the QUARTILE.INC() function:

Understanding What Function to Use to Calculate Quartiles

There is no one correct way of calculating the quartiles of a dataset. Different programming languages or tools actually use different tools to calculate quartiles. Understanding that there are different ways to calculate quartiles is half the battle.

Because the median is a robust measure, the differences between the two methods will likely be very minimal. In some cases, the values will actually be exactly the same.

Conclusion

In this tutorial, you learned how to understand the differences between the Excel QUARTILE(), QUARTILE.INC(), and QUARTILE.EXC() functions. Being able to calculate quartiles in Excel is a helpful skill to have. Having three different functions to calculate the values can be confusing. This tutorial used simple, step-by-step examples to provide an overview of the different functions. Finally, you learned that the methods can often yield very similar results, if not the same result.

Additional Resources

To learn more about related topics, check out the tutorials below:

• How To Calculate the Interquartile Range (IQR) In Excel
• How to Calculate Standard Deviation in Excel
• How to Calculate the Coefficient of Variation in Excel
• QUARTILE.INC() – Official Documentation