A to Z of Excel Functions: The QUARTILE Function

Welcome back to our regular A to Z of Excel Functions blog.  Today, we look at the QUARTILE function.

The QUARTILE function

Quartiles are three values that split sorted data into four parts, each with an equal number of observations.  Quartiles are a type of quantile (points in a distribution that relate to the rank order of values in that distribution.  They are denoted as follows:

• first quartile: also known as Q1 or the lower quartile
• second quartile: also known as Q2 or the median
• third quartile: also known as Q3 or the upper quartile.

There is one fewer quantile than the number of groups created.

QUARTILE returns the quartile of the dataset, based upon percentile values from 0..1 inclusive.  Quartiles often are used in sales and survey data to divide populations into groups.  For example, you could use QUARTILE to find the top 25% of incomes in a population.

It should be noted that this function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage (see QUARTILE.INC).  Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

QUARTILE employs the following syntax to operate:

The QUARTILE function has the following arguments:

• array: this is required and represents the array or cell range of numerical values for which you want the quartile value
• quart: this is also required and denotes which value you wish to return.

It should be noted that:

• if the array is empty, QUARTILE returns the #NUM! error value
• if quart is not an integer, it is truncated
• if quart ≤ 0 or if quart ≥ 4, QUARTILE returns the #NUM! error value
• MIN, MEDIAN and MAX theoretically return the same value as QUARTILE when quart is equal to zero [0], two [2] and four [4] respectively. 

Please see my example below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.