Quartiles are used in sales, finance, polling results, surveys, and other business and sciences areas. The quartiles of the data set values are the four subsets whose have three quartile points. In other words, there are three quartile points that divide a data set into four quartiles. Excel has a function for working with quartiles called QUARTILE.EXC.
Using Excel’s QUARTILE.EXC function
Excel QUARTILE.EXC function returns the three quartile points from a data set, based on percentile values from 0 to 1, exclusive.
It is categorized as a Statistical function. This function is available in Excel 2010 and later versions. Excel QUARTILE.EXC function can return first quartile, second quartile and third quartile for a given data set.
Format of QUARTILE.EXC
Syntax of Excel QUARTILE.EXC function is a follows;
=QUARTILE.EXC (array, quart)
It has two required arguments or parameters.
Array – It is an array or cells range containing numeric values for which you need the quartile value.
Quart – It is the quartile value to return. It is an integer between 1 and 3, representing the required quartile as shown in the following table.
If quart is equal to 1 or 3, then the array or data set must contain more than 2 values. Quart argument accepts three values:
Quart |
Return Value |
1 |
First quartile – 25th percentile |
2 |
Second quartile or median value – 50th percentile |
3 |
Third quartile – 75th percentile |
Excel QUARTILE.EXC function is exclusive, and it has “greater than” behavior. You cannot return the minimum or maximum values in data set by using this function.
Example of QUARTILE.EXC
Suppose you have an array or range of incomes values for a population group for which you want the quartile values. Following table shows how Excel QUARTILE.EXC function is used to return first, second and third quartile values for this given data set.
Formula for first quartile;
=QUARTILE.EXC($A$2:$A$8,1)
OR
Formula for second quartile or median value;
=QUARTILE.EXC($A$2:$A$8,2)
Image Title: QUARTILE.EXC_Function
Notes on QUARTILE.EXC
Following points must be remembered while using Excel QUARTILE.EXC function.
- Excel QUARTILE.EXC function returns the #NUM! error, if either; a) array is empty, b) if the quart is less than 1 or greater than 3, c) if the array has less than 3 values, and the quart is equal to 1 or 3
- If the quart value is not the numeric value, Excel QUARTILE.EXC function returns the #VALUE! error value.
- If quart is not an integer, it is truncated.
- Excel QUARTILE.EXC function cannot return the minimum or maximum values.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment