For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Math and trigonometry

QUARTILE.INC Function

Returns three quartile points from a data set, based on percentile values from 0 to 1, inclusive. Also returns the maximum and minimum values.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

According to WikiPedia, "the quartiles of a ranked set of data values are the four subsets whose boundaries are the three quartile points. Thus an individual item might be described as being 'in the upper quartile'."

Quartiles are used in polling, sociology, epidemiology, marketing, finance, and other areas of business and science.

Both QUARTILE.EXE and QUARTILE.INC return three quartile points that divide a data set into four quartiles. QUARTILE is the deprecated version of QUARTILE.INC.

Unfortunately...

  • There's not a general agreement on how quartiles should be calculated.
  • The different methods that Excel uses with QUARTILE.EXC and QUARTILE.INC aren't quick and easy to grasp.

If you really need to know how these functions calculate their differing quartile points, I recommend Jon Peltier's blog post that I reference in Other Help below. Otherwise, you might begin with QUARTILE.INC, the deprecated version of the QUARTILE function.

Syntax

QUARTILE.INC(array, quart)

  • array  Required. The array or cell range of numeric values for which you want the quartile value.

  • quart  Required. Indicates which value to return, as shown in the following table.

If Quart Equals QUARTILE.INC Returns
0 Minimum value
1 First quartile point (25th percentile)
2 Median value point (50th percentile)
3 Third quartile point (75th percentile)
4 Maximum value

Applies To

Excel 2010 and above

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

The following examples find the three quartile points then divide the data into four quartiles plus the three points. Notice that because some of the points are artificial, they arent' found in the data.

In practice, you must decide the quartile in which to group a quartile point that does exist.

Example 1

QUARTILE.EXC example with an odd number of data points

Example 1 uses an odd number of data points. In this instance, the quartile points do exist. However, that might not always be true.

Example 2

QUARTILE.EXC example with an even number of data points

Example 2 uses an even number of data points. In this example, neither quartile point exists. However, that might always be true.

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards