QUARTILE.EXC function

1 year ago

3 minutes

The QUARTILE.EXC function in Excel is used to calculate the quartiles of a dataset, excluding the lowest and highest values. Quartiles divide a dataset into four equal parts, and this function specifically uses the exclusive method of calculating quartiles, which excludes the smallest and largest values from consideration.

This function is typically used in statistical analysis to summarize the distribution of data.

Syntax

=QUARTILE.EXC(array, quart)

Parameters

array (required): The range or array of numeric data for which you want to calculate the quartile.
quart (required): Indicates which quartile value to return:
- 1: First quartile (25th percentile)
- 2: Median (50th percentile)
- 3: Third quartile (75th percentile)

Key Points

Exclusive Method: The QUARTILE.EXC function excludes the smallest and largest values from the calculation. This differs from the QUARTILE.INC function, which includes the entire dataset.
Array Sorting: Excel automatically sorts the data in the array internally when calculating the quartile.
Error Handling:
- If the quart argument is less than 1 or greater than 3, Excel returns a #NUM! error.
- If the array contains fewer than 3 data points, the function will also return a #NUM! error since at least 3 points are required for exclusive quartile calculations.

Examples

1. Basic Quartile Calculation

Suppose the dataset is {5, 10, 15, 20, 25} in range A1:A5.

To calculate the:

First Quartile:
```
=QUARTILE.EXC(A1:A5, 1)
```
Result: 10 (the 25th percentile, excluding the lowest and highest values).
Median (Second Quartile):
```
=QUARTILE.EXC(A1:A5, 2)
```
Result: 15 (the 50th percentile, excluding the lowest and highest values).
Third Quartile:
```
=QUARTILE.EXC(A1:A5, 3)
```
Result: 20 (the 75th percentile, excluding the lowest and highest values).

2. Dataset with More Points

For a dataset {3, 7, 8, 12, 13, 15, 16, 19, 21} in B1:B9:

First Quartile:
```
=QUARTILE.EXC(B1:B9, 1)
```
Result: 8
Median (Second Quartile):
```
=QUARTILE.EXC(B1:B9, 2)
```
Result: 13
Third Quartile:
```
=QUARTILE.EXC(B1:B9, 3)
```
Result: 19

3. Error for Insufficient Data

If the dataset is {1, 2} in C1:C2, and you attempt to calculate any quartile:

=QUARTILE.EXC(C1:C2, 1)

Result: #NUM! (because the exclusive method requires at least 3 data points).

Notes

Difference Between QUARTILE.EXC and QUARTILE.INC:
- QUARTILE.EXC excludes the smallest and largest values when calculating quartiles.
- QUARTILE.INC includes all data points in the calculation.
Use Cases: The QUARTILE.EXC function is often used in box plot creation, data distribution analysis, and to identify outliers.
Quartile Definitions:
- First Quartile (Q1): Separates the lowest 25% of the data.
- Second Quartile (Median/Q2): Separates the dataset into two equal halves (50th percentile).
- Third Quartile (Q3): Separates the highest 25% of the data.

Related Functions

QUARTILE.INC: Calculates quartiles including all data points.
PERCENTILE.EXC: Returns the k-th percentile of a dataset using the exclusive method.
PERCENTILE.INC: Returns the k-th percentile of a dataset using the inclusive method.
MEDIAN: Returns the median (50th percentile) of a dataset.

The QUARTILE.EXC function is particularly useful in statistical scenarios where excluding outliers or extreme values is required to get a better sense of the central data distribution.

Post Views: 11