Description
Use this function toreturn the quartile of a dataset based on percentile values including the minimum and maximum values in the calculation.
QUARTILE is useful for dividing a numeric dataset into four equal parts and finding values at specific quartile positions. It's commonly used in statistical analysis and data visualization.
Syntax
QUARTILE(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
Sample data
| A | B |
|---|---|
| Value | Description |
| 1 | Minimum |
| 3 | |
| 5 | Median |
| 7 | |
| 9 | Maximum |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the minimum value in a data set. | =QUARTILE(A2:A7, 0) |
Returns the smallest value in the data set. Result: 1 |
| Find the value marking the bottom 25% of a data set. | =QUARTILE(A2:A7, 1) |
Returns the value at the 25th percentile of the sorted data set. Result: 3 |
| Find the middle value (median) of a data set. | =QUARTILE(A2:A7, 2) |
Returns the value at the 50th percentile of the sorted data set. Result: 5 |
| Find the value marking the top 25% of a data set. | =QUARTILE(A2:A7, 3) |
Returns the value at the 75th percentile of the sorted data set. Result: 7 |
| Find the maximum value in a data set. | =QUARTILE(A2:A7, 4) |
Returns the largest value in the data set. Result: 9 |
Notes
- QUARTILE is functionally the same as QUARTILE.INC.
- QUARTILE uses linear interpolation to calculate quartile values that fall between data points.
- QUARTILE uses (n-1) intervals for n data points, and includes the smallest and greatest values in the dataset when calculating quartiles.
- 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.
- Use QUARTILE.EXC if you want to exclude the 0 and 1 percentile values.
- If
arrayis empty or contains more than 8,191 data points, QUARTILE 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 Workiva spreadsheet 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 can give slightly different results than QUARTILE.EXC.
Tips
- Use QUARTILE to quickly identify the spread and central tendency of a dataset.
- Combine QUARTILE with other statistical functions such as AVERAGE and STDEV for comprehensive data analysis.
- Use QUARTILE to create box plots or box-and-whisker diagrams for data visualization.
- Consider using QUARTILE.INC or QUARTILE.EXC for more precise control over the quartile calculation method.
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