Description
Use this function calculate the quartiles of a data set, which divide the data into four equal parts, excluding the minimum and maximum values from the calculations.
Quartiles are useful in identifying outliers and understanding the distribution of your data. QUARTILE.EXC returns the quartile of a data set, based on percentile values exclusive of the minimum and maximum values.
Syntax
QUARTILE.EXC(array, quart)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The array or range of numeric data for which you want to calculate the quartile. | An array of numbers or a reference to a range of cells containing numbers. Up to 8,191 values can be provided. |
quart |
Yes | Indicates which quartile value to return. | An integer from 0 to 4: 0 = Minimum value 1 = First quartile (25th percentile) 2 = Median (50th percentile) 3 = Third quartile (75th percentile) 4 = Maximum value |
Example
Data
| A |
|---|
| Values |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
Examples
| Formula | Description | Result |
|---|---|---|
=QUARTILE.EXC(A2:A11, 1) |
Returns the first quartile (25th percentile) of the data set | 3.25 |
=QUARTILE.EXC(A2:A11, 2) |
Returns the median (50th percentile) of the data set | 5.5 |
=QUARTILE.EXC(A2:A11, 3) |
Returns the third quartile (75th percentile) of the data set | 7.75 |
Notes
- QUARTILE.EXC uses linear interpolation to calculate quartile values that fall between data points.
- QUARTILE.EXC uses (n+1) intervals for n data points, and excludes the smallest and greatest values in the dataset when calculating quartiles.
- Use QUARTILE.INC if you want to include the 0 and 1 percentile values.
- The first quartile (Q1) is the 25th percentile, the second quartile (Q2 or median) is the 50th percentile, and the third quartile (Q3) is the 75th percentile, but these values are calculated in a way that excludes the extremes.
- If
arrayis empty or contains more than 8,191 data points, QUARTILE.EXC returns a #NUM! error. - For accurate results, the data set should contain numeric values only.
- Empty cells and text values are not included in the calculations.
- Unlike Excel, logical values are ignored in the quartile calculations.
- If any cell in the data set contains an error value (#N/A, #VALUE!, #DIV/0!, etc.), the function will return an error.
- If
quartis not an integer, it is truncated. - If
quart< 0 orquart> 4, QUARTILE returns a #NUM! error. - For smaller datasets, QUARTILE.EXC can give slightly different results than QUARTILE.INC.
Tips
- Use QUARTILE.EXC to divide your data into quartiles for better data analysis and interpretation.
- You can combine QUARTILE.EXC with other statistical functions such as MEDIAN and PERCENTILE.EXC for more comprehensive data analysis.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA