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 |
Examples
Sample data
| A |
|---|
| Values |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the value marking the bottom 25% of a data set, excluding the extremes. | =QUARTILE.EXC(A2:A11, 1) |
Interpolates between the 2nd and 3rd values (2 and 3) in the sorted data set to find the 25th percentile. Result: 2.75 |
| Find the middle value (median) of a data set, excluding the extremes. | =QUARTILE.EXC(A2:A11, 2) |
Averages the 5th and 6th values (5 and 6) in the sorted data set to find the 50th percentile. Result: 5.5 |
| Find the value marking the top 25% of a data set, excluding the extremes. | =QUARTILE.EXC(A2:A11, 3) |
Interpolates between the 8th and 9th values (8 and 9) in the sorted data set to find the 75th percentile. Result: 8.25 |
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