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

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₂- Q₁ =Q₃- Q₂
Positively skewed: Q₂- Q₁ < Q₃- Q₂
Negatively skewed: Q₂- Q₁ > Q₃- Q₂

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.

Excel function categories

Excel categories

Leave a Comment