The following article introduces you to the QUARTILE.EXC function - one of the functions in the group of statistical functions that is very popular in Excel.
Description: The function returns the quartile of a dataset, with percentile values from 0 to 1 excluding 0 and 1. The function supports Excel 2010 and later versions.
Syntax: QUARTILE.EXC (array, quart)
Inside:
- array: An array or range containing values that want to find quartiles, a required parameter .
- quart: The position of the quartile to be returned, is a mandatory parameter consisting of 3 values 1, 2 and 3. In addition to these values, the function returns an error value.
Attention:
- If quart is not an integer -> it is truncated to an integer.
- If array is empty -> the function returns the #NUM! Error value .
- If quart ≥ 4 or quart ≤ 0 -> the function returns the #NUM! Error value
- The MIN and MAX, MEDIAN functions return the same value as the QUARTILE.EXC function with corresponding values quart = 0, quart = 4, quart = 2.
For example:
Locate the units as described in the following data table:
- Determine the position of the 1st percentile. In the cell to calculate enter the formula : = QUARTILE.EXC (D6: J6, D7)
- Press Enter -> 1st percentile is:
- The 6th percentile. In the cell to calculate enter the formula: = QUARTILE.EXC (D6: J6,4). Press Enter -> return value is:
Here due to the quart value = 6> 4 -> the function returns the #NUM! Error value . The quart value is only in 3 values: 1, 2 and 3.
Above are instructions and some specific examples when using QUARTILE.EXC function in Excel.
Good luck!