The 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 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 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
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 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
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
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.
Leave a Comment