A to Z of Excel Functions: The QUARTILE.EXC Function

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

The QUARTILE.EXC 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.EXC returns the quartile of the dataset, based upon percentile values from 0..1 exclusive.

QUARTILE.EXC employs the following syntax to operate:

The QUARTILE.EXC 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.EXC returns the #NUM! error value
• if quart is not an integer, it is truncated
• if quart ≤ 0 or if quart ≥ 4, QUARTILE.EXC returns the #NUM! error value
• MIN, MEDIAN and MAX theoretically return the same value as QUARTILE.EXC when quart is equal to zero [0], two [2] and four [4] respectively.  It should be noted that the extremes will more likely return #NUM! due to the fact the range is exclusive (i.e. it excludes the extremities).

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.