Related Functions:

The QUARTILE.EXC function returns the quartile of the data set, based on percentile values from 0…1, exclusive.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.

Syntax

=QUARTILE.EXC(array,quart)

Arguments

Argument Description
array The range of data values for which you want to calculate the specified quartile
quart An integer between 1 and 3, representing the required quartile

  If quart = 1 or 3, the supplied array must contain more than 2 values

Note: The QUARTILE.EXC function can’t calculate the 0’th or the 4’th quartile as the calculation is based on the range 0 to 1 exclusive.

If the requested quartile falls between two of the values in the supplied array, Excel interpolates between these values to calculate the quartile value.

Examples

  A B C D E
1 Data   Formula Result Notes
2 9   =QUARTILE.EXC(A2:A10,1) 11.5 Locates the position of the first quartile
3 37   =QUARTILE.EXC(A2:A10,2) 20 Locates the position of the second quartile
4 6   =QUARTILE.EXC(A2:A10,3) 32 Locates the position of the third quartile
5 16        
6 26        
7 32        
8 32        
9 14        

Note: The Quartile.Inc function is closely related to the PERCENTILE.EXC such that:

  • The 1st quartile is the same as the 25th percentile
  • The 2nd quartile is the same as the 50th percentile
  • The 3rd quartile is the same as the 75th percentile

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the supplied value of quart cannot be interpreted as a numeric value
#NUM! Occurs if either:

  the supplied value of quart is is < 1 or > 3
  the supplied array is empty
  the supplied array has fewer than 3 values, and quart is 1 or 3

The 1st, 2nd, 3rd or 4th quartile of a range of data is the value that 25%, 50%, 75% or 100% (respectively) of the data values fall within.

The QUARTILE.INC and QUARTILE.EXC functions both find a requested quartile of a supplied data set. The difference between these two functions is that the QUARTILE.INC function bases its calculation on a percentile range of 0 to 1 inclusive, while the QUARTILE.EXE function bases its calculation on a percentile range of 0 to 1 exclusive.

See Wikipedia for more information on quartile.