How to use the QUARTILE function
What is the QUARTILE function?
The QUARTILE function returns the quartile of a data set, use the QUARTILE function to divide data into groups.
What is a quartile?
A quartile is a type of quantile which splits a dataset into four equal parts. The quartiles divide a rank-ordered dataset into four quarters.
There are three quartile values - Q1, Q2, and Q3:
Q1 (first quartile) - 25th percentile
Q2 (second quartile) - 50th percentile (median)
Q3 (third quartile) - 75th percentile
What is the interquartile range (IQR)?
The interquartile range (IQR) is the difference between Q3 and Q1. It indicates the middle 50% spread of the data.
When is it useful to calculate the quartiles?
Quartiles provide quantile-based partitioning of data that reveals distribution, spread, skewness, and outliers. They serve as an important basic statistical summary.
What is quantile?
Quantiles are a statistical technique for dividing a dataset into equal-sized groups for analysis. Quantiles split data into equal-sized subsets when ordered from smallest to largest.
The quartiles, percentiles, quintiles, deciles, etc. are examples of quantiles.
- Quartiles split data into 4 equal groups
- Percentiles into 100 groups,
- Quintiles into 5.
Quantiles can show aspects of shape, spread, and concentration.
How to graph quartiles?
The Box and Whisker chart display quartiles.
QUARTILE Function Syntax
QUARTILE(array, quart)
QUARTILE Function Arguments
array | Required.The cell values for which you want to calculate the quartile value. |
quart | Required. Indicates which value to return, see table below. |
Quart parameters | |
---|---|
0 | Minimum value. |
1 | First quartile (25th percentile). |
2 | Median quartile (50th percentile). |
3 | Third quartile (75th percentile). |
4 | Maximum value. |
QUARTILE Function Example
This example demonstrates the QUARTILE function, the image above shows the following values in B3:B11: 66, 97, 99, 77, 9, 60, 35, 60, 61
Formula in cell D3:
Here is how the QUARTILE function calculates the first Quartile (Q1).Â The median is 61 based on the numbers in cell range B3:B11. The first half contains the following numbers: 9, 35, 61, 60, and 60 The median of these numbers is 60.
The QUARTILE function returns the same value as:
- MIN function if quart is 0 (zero)
- MEDIAN function if quart is 2.
- MAX function if quart is 4
What is the MEDIAN?
The median value is the middle number of a group of numbers when arranged from smallest to largest. It returns a good measure of the typical value in a data set that is not affected by outliers or skewness . For example, the median income may be a better measure of the average income than the mean. The mean can be distorted by a few very high incomes which is the case, for instance, in the US. The very high incomes represent the outliers.
For example, a group containing an odd number of values like five numbers: 1, 3,4,9, and 11 has the middle value 4. 1 and 3 are smaller and 9 and 11 are larger.
A group containing an even number of values like: 1,3,9, and 11 has a median of 6. There are two middle numbers in the group which are 3 and 9. 3 + 9 = 12 and 12/2 equals 6.
What is the MIN function?
The MIN function returns the smallest number in a cell range.
Function syntax: MIN(number1, [number2], ...)
What is the MEDIAN function?
The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
Function syntax: MEDIAN(number1, [number2], ...)
What is the MAX function?
The MAX function calculate the largest number in a cell range.
Function syntax: MAX(number1, [number2], ...)
QUARTILE Function not working
The QUARTILE function returns #NUM! value if
- quart < 0 (zero)
- quart > 4
How is the QUARTILE function calculated?
QUARTILE function calculates quartiles using the inclusive method:
Data is sorted lowest to highest. The quartile boundaries include the quartile values themselves.
For example, for the data {1, 2, 3, 4, 5}:
Q0 = 1
Q1 = 2
Q2 = 3
Q3 = 4
The median of data {1, 2, 3, 4, 5} is 3. The first halfÂ contains 1 , 2, and 3. The median of 1,2, and 3 is 2 = Q1
Q2 is the median: 3
The second halfÂ contains 3, 4 and 5, The median of 3, 4 and 5 is 4 = Q3
Functions in 'Compatibility' category
The QUARTILE function function is one of 21 functions in the 'Compatibility' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form