In financial, sales and survey analysis you often divide the dataset into population groups and you need to find the top 25% of revenue or incomes for a given population group. In financial analysis, you often need to find the minimum or maximum revenue or 1st or 2nd quartile for given figures. In Excel QUARTILE.INC function is designed to perform such calculations.
Excel QUARTILE.INC function returns the quartile from a set of values, based on the percentile range of 0 to 1 (inclusive).
It is categorized as Statistical function and is available in Excel 2010 or later versions. However, Excel QUARTILE.INC function is simply an updated version of QUARTILE function that is available in earlier versions of Excel.
Syntax of Excel QUARTILE.INC function is;
This function uses the following arguments:
Array – It is the array or cell range of data values for which you need the quartile value.
Quart – It is an integer between 0 and 4, representing the required quartile. Five values starting from 0 to 4 can be inserted for the quart argument, as shown in the table below:
|0||Minimum value in the dataset|
|1||First quartile (25th percentile)|
|2||Second quartile (50th percentile)|
|3||Third quartile (75th percentile)|
|4||Maximum value in the dataset|
Suppose you have data set of revenue figures for the last 8 months and you need to find minimum value, first quartile, second quartile, third quartile, and maximum value.
As Excel QUARTILE.INC function returns quartile from the range of values based on percentile values, so you need to use;
- 0 for the minimum value
- 1 for the 25th percentile (first quartile)
- 2 for the 50th percentile (second quartile)
- 3 for the 75th percentile (third quartile)
- 4 for the Max value.
Based on this example, let’s have a look at the Excel QUARTILE.INC function and how it calculates and returns the quartile based on percentile values.
The formula for minimum value;
Formula for First Quartile (25th percentile) value;
There are some points to remember while using Excel QUARTILE.INC function.
- This function may return #NUM! error, if either given value of quart is < 0 or > 4, or given data range is empty
- This function may return #VALUE! error, if the quartile value is non-numeric
- If quart argument is not an integer, it is truncated.
- If the quartile falls between two values, Excel returns the intermediate or surrounding value between two values to calculate the quartile value.