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
Table of Contents
- Introduction
- QUARTILE.EXC Function Syntax
- QUARTILE.EXC Function arguments
- QUARTILE.EXC Function Example 1
- QUARTILE.EXC Function Example 2
- QUARTILE.EXC Function Example 3
- QUARTILE.EXC Function Example 4
- QUARTILE.EXC Function not working
- How is the QUARTILE.EXC Function calculated?
- Get Excel *.xlsx file
1. Introduction
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.
Symmetrical distribution: Q_{2}- Q_{1} =Q_{3}- Q_{2}
Positively skewed: Q_{2}- Q_{1} < Q_{3}- Q_{2}
Negatively skewed: Q_{2}- Q_{1} > Q_{3}- Q_{2}
Learn more about skewness: SKEW function
What is a 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.
2. QUARTILE.EXC Function Syntax
QUARTILE.EXC(array, quart)
3. 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. |
The quart argument allows you to use the following parameters:
Quart parameters | |
---|---|
0 | Minimum value. (Microsoft documentation is wrong, this argument returns a #NUM value.) Use the MIN function to calculate the smallest value in a data set. |
1 | First quartile (25th percentile). |
2 | Median quartile (50th percentile). |
3 | Third quartile (75th percentile). |
4 | Maximum value. (Microsoft documentation is wrong, this argument returns a #NUM value.) Use the MAX function to calculate the largest value in a data set.. |
4. QUARTILE.EXC Function Example 1
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
The arguments are:
- array = B3:B11
- quart = 1
Formula in cell D3:
The function returns 47 which represents the first quartile in B3:B11.
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
5. QUARTILE.EXC Function Example 2
A teacher has recorded the scores of 45 students in a test. What are the values of the first quartile (25th percentile), second quartile (50th percentile or median), and third quartile (75th percentile) of the student scores?
Here are the scores:
41 | 51 | 75 | 54 | 48 | 24 | 36 | 67 | 79 |
64 | 67 | 53 | 49 | 29 | 69 | 68 | 72 | 53 |
38 | 59 | 42 | 71 | 52 | 45 | 48 | 59 | 54 |
70 | 46 | 45 | 68 | 31 | 41 | 54 | 24 | 62 |
37 | 29 | 86 | 63 | 59 | 63 | 80 | 57 | 44 |
The arguments are:
- array = B22:B66
- quart = 1
This formula calculates the first quartile meaning 25th percentile (25%). These values are smaller than 43: 24, 24, 29, 29, 31, 36, 37, 38, 41, 41, and 42
Formula in cell D15:
The following formula in D16 calculates the second quartile meaning 50th percentile (50%). The values between the 1st and 2nd quartile are: 44, 45, 45, 46, 48, 48, 49, ,51, 52, 53, and 53.
Formula in cell D16:
This formula calculates the third quartile meaning 75th percentile (75%). These values are larger than or equal to 67: 67, 67, 68, 68, 69, 70, 71, 72, 75, 79, 80, and 86
Formula in cell D17:
6. QUARTILE.EXC Function Example 3
A retail store tracks the daily sales of a particular product over the last 100 days. The store wants to identify the first, second, and third quartiles of the daily sales data to understand the distribution of sales?
Here are the values:
812 | 1057 | 711 | 585 | 1006 |
1077 | 951 | 828 | 1143 | 991 |
1051 | 1065 | 1206 | 1014 | 1092 |
822 | 1087 | 689 | 998 | 771 |
966 | 968 | 971 | 733 | 390 |
892 | 506 | 814 | 781 | 1357 |
489 | 1312 | 646 | 971 | 327 |
756 | 1044 | 1272 | 902 | 736 |
761 | 815 | 723 | 1332 | 1058 |
872 | 1128 | 1244 | 698 | 1132 |
721 | 977 | 1102 | 1157 | 1315 |
498 | 1394 | 778 | 654 | 1528 |
1043 | 577 | 731 | 1108 | 883 |
1059 | 1639 | 780 | 1155 | 566 |
1236 | 1300 | 1639 | 1415 | 1167 |
1128 | 1246 | 1303 | 1109 | 1119 |
915 | 891 | 856 | 1298 | 854 |
996 | 1271 | 1016 | 896 | 1144 |
1218 | 940 | 1826 | 478 | 1039 |
825 | 527 | 1132 | 592 | 663 |
The arguments are:
- array = B22:B121
- quart = 1
This formula calculates the first quartile meaning 25th percentile (25%). The first quartile is 772.75.
Formula in cell D15:
The following formula in D16 calculates the second quartile meaning 50th percentile (50%). The second quartile is 984.
Formula in cell D16:
This formula calculates the third quartile meaning 75th percentile (75%). The third quartile is 1140.25.
Formula in cell D17:
The image above shows a box and whisker chart, it contains the values that represent the quartiles and also the smallest and largest values.
7. QUARTILE.EXC Function Example 4
A real estate agency has compiled data on the prices of houses sold in a specific neighborhood over the past year. To better understand the market, they want to calculate the first, second, and third quartiles of the house prices.
Here are the values:
298,567 | 409,478 | 496,197 | 265,195 | 298,445 |
352,253 | 473,740 | 511,783 | 422,252 | 370,326 |
376,678 | 325,961 | 489,498 | 534,916 | 388,736 |
333,298 | 423,778 | 380,452 | 517,917 | 482,795 |
The arguments are:
- array = B22:B41
- quart = 1
This formula calculates the first quartile meaning 25th percentile (25%). The first quartile is 338.037.
Formula in cell D15:
The following formula in D16 calculates the second quartile meaning 50th percentile (50%). The second quartile is 399,107.
Formula in cell D16:
This formula calculates the third quartile meaning 75th percentile (75%). The third quartile is 487,822.
Formula in cell D17:
The image above shows a box and whisker chart, it contains the values that represent the quartiles and also the smallest and largest values.
8. 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.
9. 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 73 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