Note
In Excel 2010, the QUARTILE() function was replaced with the QUARTILE.EXC() and QUARTILE.INC() functions to increase the accuracy of the results. To ensure the backward compatibility of QUARTILE.EXC() and QUARTILE.INC(), the QUARTILE() function is still available.
Syntax. QUARTILE(array,quart)
Definition. This function returns the quartile of a data set. Quartiles are often used in sales and survey data to divide populations into groups. For example, you can use QUARTILE() to find the top 25 percent of incomes in a population.
The quart argument indicates which value to return. Table 12-3 lists the values for this argument.
Arguments
array (required). The array or cell range of numeric values for which you want the quartile value
quart (required). Indicates which value to return
Note
If array is empty, QUARTILE() returns the #NUM! error value. If quart isn’t an integer, the decimal places are truncated. If quart is less than 0 or greater than 4, the QUARTILE() function returns the #NUM! error.
MIN(), MEDIAN(), or MAX() return the same value as QUARTILE() if the quartile is 0, 2, or 4, respectively.
Background. By using quantiles and quartiles, you can calculate the distribution of data. Whereas the median divides the distribution in half, the quantile divides the distribution into several equal parts, and the quartile divides the distribution into four equal parts.
The QUARTILE() function requires the array argument indicating the range to be evaluated and the quart argument indicating the quartile to be returned. This can be any value from 0 through 4. Table 12-3 lists the values for the quart argument.
Table 12-3. The Values for the quart Argument
Value | Result |
|---|---|
0 | Smallest value (minimum) |
1 | Lower quartile (25 percent quantile) |
2 | Median (50 percent quantile) |
3 | Upper quartile (75 percent quantile) |
4 | Largest value (maximum) |
If quartile 0 is the minimum, quartile 4 the maximum, and quartile 2 the median, then quartiles 1 and 3 can be easily calculated.
Example. A pharmaceutical company that produces pills for headaches and sells these pills throughout the entire country wants to evaluate the pill sales in different regions numbered 1 through 5 (Texas, Virginia, California, Oregon, and Washington) for every 100,000 residents in each state. The goal is to calculate how much of the sales is 25 percent of the sales in a state in one year (see Figure 12-119). You know the sales for a year (12 values) per state. To calculate the quartile, you have to sort the values in ascending order.
The sales for Region 1 indicate that the smallest value is the minimum, the largest value is the maximum, and quartile 2 (50 percent and the median) is 4,210.
How can you calculate the second quartile without using the QUARTILE() function? Because the number of values is even, the 50 limit has to be between the fifth and sixth value. If you add both values and divide the result by 2, you get the median. If there were 13 values, the median or the second quartile would be the seventh value.
You already have the following quartiles:
Quartile 0 = 800
Quartile 2 = 4,210
Quartile 4 = 11,786
Now you still need quartile 1 and quartile 3 (the 25 percent and 75 limit percentile). Therefore, you have to find the values at position 0.25 and 0.75 based on the original data. You need to calculate the values for which 25 percent of the observations are smaller than or equal to the value (the first quartile) and 75 percent of the observations are greater than or equal to it (the third quartile).
You have 12 number values (n). If you multiply n by 0.25 and 0.75, you find out that the first quartile is between the third and fourth values and the third quartile is between the ninth and tenth values of the sorted original data.
The QUARTIL() function returns the following results for the first and the third quartile:
Quartile 1 = 1,185
Quartile 3 = 5,525
The first quartile is between the third and the fourth values but not in the middle and more towards the fourth value.
Now you can draw the following conclusions:
In Region 1, 25 percent of the sales are up to $1,185.00.
In Region 5, 75 percent of the sales are up to $3,840.00.