Description
Use this function tocalculate the quartiles of a data set, which divide the data into four equal parts, including the minimum and maximum values in the calculation.
Quartiles are useful in identifying outliers and understanding the distribution of your data. QUARTILE.INC returns the quartile of a data set, based on percentile values from 0 to 1, inclusive.
Syntax
QUARTILE.INC(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 |
||
|---|---|---|
| 1 | Values | |
| 2 | 11 | |
| 3 | 17 | |
| 4 | 17 | |
| 5 | 3 | |
| 6 | 66 | |
| 7 | 24 | |
| 8 | 99 | |
| 9 | 43 | |
| 10 | 45 | |
| 11 | 12 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the value marking the bottom 25% of a data set. | =QUARTILE.INC(A2:A11, 1) |
Interpolates between the 3rd and 4th values (3 and 4) in the sorted data set to find the 25th percentile. Result: 13.25 |
| Find the middle value (median) of a data set. | =QUARTILE.INC(A2:A11, 2) |
Averages the 5th and 6th values (5 and 6) in the sorted data set to find the 50th percentile. Result: 33.5 |
| Find the value marking the top 25% of a data set. | =QUARTILE.INC(A2:A11, 3) |
Interpolates between the 7th and 8th values (7 and 8) in the sorted data set to find the 75th percentile. Result: 60.75 |
Notes
- QUARTILE.INC functionally operates the same as QUARTILE, using linear interpolation to calculate quartile values that fall between data points.
- QUARTILE.INC uses (n-1) intervals for n data points, and includes the smallest and greatest values in the dataset when calculating quartiles.
- Use QUARTILE.EXC if you want to exclude 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.
- If
arrayis empty or contains more than 8,191 data points, QUARTILE.INC 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
quartisn't an integer, it is truncated. - If
quart< 0 orquart> 4, QUARTILE returns a #NUM! error. - For smaller datasets, QUARTILE.INC can give slightly different results than QUARTILE.EXC.
Tips
- Use QUARTILE.INC to divide your data into quartiles for better data analysis and interpretation.
- You can combine QUARTILE.INC with other statistical functions such as MEDIAN and PERCENTILE.INC for more comprehensive data analysis.