Differences between QUARTILE.EXC function and QUARTILE.INC function in Microsoft Excel

When analyzing data in Microsoft Excel, users often need to understand the distribution of their datasets. Two functions that help with this task are QUARTILE.EXC and QUARTILE.INC. While they may seem similar, they serve slightly different purposes. Let’s explore these functions, their syntax, and how to use them effectively.

Key Takeaways

• QUARTILE.EXC calculates quartiles excluding the lowest and highest values.
• QUARTILE.INC includes all data points when calculating quartiles.
• The choice between the two depends on the specific requirements of your data analysis.

Understanding QUARTILE.EXC and QUARTILE.INC

QUARTILE.EXC (Exclusive) is designed to compute quartiles for a dataset while excluding the minimum and maximum values. This means it is better suited for datasets where outliers (extreme values) might skew the quartiles, leading to more balanced results.

QUARTILE.INC (Inclusive), on the other hand, takes all data points into account, including the highest and lowest. This function reflects the full range of data, which can be beneficial for datasets that are complete and free from outliers.

Syntax and Arguments

QUARTILE.EXC

The syntax for QUARTILE.EXC is as follows:

excel
QUARTILE.EXC(array, quart)

• array: A range of data or an array containing the numerical values.
• quart: An integer (0-3) that specifies which quartile to return:
  • 0 – Minimum value
  • 1 – First quartile (25th percentile)
  • 2 – Median (50th percentile)
  • 3 – Third quartile (75th percentile)
  • Note: This function does not accept 4 (maximum).

QUARTILE.INC

The syntax for QUARTILE.INC is quite similar:

excel
QUARTILE.INC(array, quart)

• array: A range of data or an array of numerical values.
• quart: An integer (0-4), where:
  • 0 – Minimum value
  • 1 – First quartile (25th percentile)
  • 2 – Median (50th percentile)
  • 3 – Third quartile (75th percentile)
  • 4 – Maximum value

Key Differences

While both functions aim to calculate quartiles, several critical distinctions exist:

1. Inclusion of Extremes:

   • QUARTILE.EXC excludes the minimum and maximum values, potentially resulting in a more representative measure of central tendency for outlier-prone datasets.
   • QUARTILE.INC includes both extremes, ensuring a complete view of the dataset.

2. Quartile Range:

   • QUARTILE.EXC only supports quartiles 0 through 3.
   • QUARTILE.INC allows calculations for quartiles 0 through 4.

3. Applicability:

   • Use QUARTILE.EXC when you want to eliminate outliers to get a clearer picture of your data.
   • Use QUARTILE.INC when analyzing complete datasets where extreme values are valid contributors.

Examples in Action

To illustrate the difference between these two functions, consider the following dataset:

Using the dataset above, let’s see how both functions return values for the first and third quartiles.

Example Calculation:

1. First Quartile (Q1):

   • Using QUARTILE.EXC:
     excel
     =QUARTILE.EXC(A1:A7, 1)

     Result: 20

   • Using QUARTILE.INC:
     excel
     =QUARTILE.INC(A1:A7, 1)

     Result: 20

2. Third Quartile (Q3):

   • Using QUARTILE.EXC:
     excel
     =QUARTILE.EXC(A1:A7, 3)

     Result: 60

   • Using QUARTILE.INC:
     excel
     =QUARTILE.INC(A1:A7, 3)

     Result: 50

Conclusion: When to Use Each Function

Choosing between QUARTILE.EXC and QUARTILE.INC depends on the characteristics of your dataset and the purpose of your analysis.

• If your data contains outliers that may distort the distribution, QUARTILE.EXC is the appropriate choice. It provides quartiles that reflect only the central tendency of the core data points.

• Conversely, use QUARTILE.INC if your data is complete and you want to include all extreme values for a more holistic view of the dataset. It ensures that the calculations consider every data point, including the highest and lowest, giving a complete perspective on range and variation.

By understanding these differences, Excel users can make informed decisions about which quartile function best serves their analytical needs.