QUARTILE.INC function

1 year ago

3 minutes

The QUARTILE.INC function in Excel calculates the quartiles of a dataset, including all values in the dataset (both the smallest and largest). Quartiles divide data into four equal parts, with each quartile representing 25% of the data distribution.

This function is commonly used for statistical analysis to summarize the spread and central tendency of a dataset.

Syntax

=QUARTILE.INC(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:
- 0: Minimum value
- 1: First quartile (25th percentile)
- 2: Median (50th percentile)
- 3: Third quartile (75th percentile)
- 4: Maximum value

Key Points

Inclusive Method: The QUARTILE.INC function includes all values in the dataset when calculating quartiles.
Array Sorting: Excel automatically sorts the data in the array internally when calculating quartiles.
Error Handling:
- If the quart argument is less than 0 or greater than 4, Excel returns a #NUM! error.

Examples

1. Basic Quartile Calculation

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

To calculate the:

Minimum value:
```
=QUARTILE.INC(A1:A5, 0)
```
Result: 5
First Quartile (25th percentile):
```
=QUARTILE.INC(A1:A5, 1)
```
Result: 10
Median (50th percentile):
```
=QUARTILE.INC(A1:A5, 2)
```
Result: 15
Third Quartile (75th percentile):
```
=QUARTILE.INC(A1:A5, 3)
```
Result: 20
Maximum value:
```
=QUARTILE.INC(A1:A5, 4)
```
Result: 25

2. Dataset with More Points

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

First Quartile (25th percentile):
```
=QUARTILE.INC(B1:B9, 1)
```
Result: 8
Median (50th percentile):
```
=QUARTILE.INC(B1:B9, 2)
```
Result: 13
Third Quartile (75th percentile):
```
=QUARTILE.INC(B1:B9, 3)
```
Result: 19

Notes

Difference Between QUARTILE.INC and QUARTILE.EXC:
- QUARTILE.INC includes all data points, including the minimum and maximum, in the calculation.
- QUARTILE.EXC excludes the minimum and maximum values when calculating quartiles.
Quartile Definitions:
- 0: Minimum value
- 1: First Quartile (25th percentile, separates the lowest 25% of the data)
- 2: Median (50th percentile, separates the dataset into two equal halves)
- 3: Third Quartile (75th percentile, separates the highest 25% of the data)
- 4: Maximum value
Use Cases: The QUARTILE.INC function is often used in data analysis, box plot creation, and to identify outliers by comparing values to the interquartile range (IQR).

Related Functions

QUARTILE.EXC: Calculates quartiles excluding the smallest and largest values.
PERCENTILE.INC: Returns the k-th percentile of a dataset using the inclusive method.
PERCENTILE.EXC: Returns the k-th percentile of a dataset using the exclusive method.
MEDIAN: Returns the median (50th percentile) of a dataset.
MIN: Returns the smallest value in a dataset.
MAX: Returns the largest value in a dataset.

The QUARTILE.INC function is a versatile tool for summarizing data distributions and identifying key points within a dataset, making it invaluable for descriptive statistics and exploratory data analysis.

Post Views: 10