How to use the QUARTILE.EXC function
What is the QUARTILE.EXC function?
The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. Quartiles split data into fourths, and QUARTILE.EXC calculates them using the exclusive method, excluding the quartile values from the boundaries.
This function was introduced in Excel 2010 has replaced the old QUARTILE function
What is QUARTILE.EXC an abbreviation from?
QUARTILE.EXC is an abbreviation of QUARTILE EXCLUSIVE.
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.EXC function example
This example demonstrates the QUARTILE.EXC 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.EXC function calculates the first Quartile (Q1). The median is 61. The first half contains the following numbers: 9, 35, 60, and 60 The median of these numbers is 47.5, here is how: 35+60=95, 95/2 equals 47.5
QUARTILE.EXC function Syntax
QUARTILE.EXC(array, quart)
QUARTILE.EXC 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.EXC function not working
The QUARTILE.EXC function returns #NUM! value if
- quart < 1
- quart > 4
The QUARTILE.EXC function returns the same value as the MEDIAN function if quart is 2.
How is the QUARTILE.EXC function calculated?
QUARTILE.EXC calculates quartiles using the exclusive method:
Data is sorted lowest to highest. The quartile boundaries exclude the quartile values themselves.
For example, for the data {1, 2, 3, 4, 5}:
Q1 = 1.5
Q2 = 3
Q3 = 4.5
The median of data {1, 2, 3, 4, 5} is 3. The first half contains 1 and 2, 2+1 = 3 3/2 = 1.5 = Q1
Q2 is the median: 3
The second half contains 4 and 5, 4+5 = 9 9/2 = 4.5 = Q3
Functions in 'Statistical' category
The QUARTILE.EXC function function is one of many functions in the 'Statistical' 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