In statistics, the quartiles, of a ranked set of data values, are the three points that divide the set into four equal groups. Each group constitutes a quarter of the data.
Practice File:
Transcript
The first quartile is the middle value between the smallest number and the middle number of the dataset. The second is the middle number of the whole dataset. And the third is the middle value between the middle number and the highest number of the dataset.
So the first quartile is at the 25% of our population of numbers, the second at 50% and the third at 75%.
Excel since its 2010 version implements two methods of calculating quartiles. Let’s try to explain the difference between them.
In this example we have a group of 12 numbers in ascending order. The inclusive function uses the (n-1) method to calculate the quartiles position. N is the total numbers we have which is 12 in our example, so n-1 equals 11.
If we multiply eleven by 0,25 we get the position of the first quartile which is in our case is 2,75. In position 2 we have the number 8 and in position 3 the number 12. Using simple mathematics, we can calculate the number in position 2.75 as the number 11. Using the same technique, we can calculate the second and third quartile as well. Excel saves us all this trouble with the inclusive version of quartile.
The exclusive version uses the (n+1) method to calculate the quartiles position. N is 12 so N+1 equals 13.
We multiply this number by 0.25 as before we get the position of the first quartile which in our case is 3.25. Notice that all numbers are shifted one place to the right. This places the first quartile between 8 and 12 again but at a different relative position between them. So the first quartile now is the number 9. The same applies to the rest of the quartiles. The exclusive version of quartile does the heavy lifting and calculates the quartiles using this method.
Let us try to use these function in the following example.
Suppose we want to calculate the third quartile for all the grades, of all the lessons in the table.
We use the inclusive version. Then select the table, and finally number three, for the third quartile.
We repeat the same procedure using the exclusive version. We select the table, and then number 3.
We notice the difference of the results they produce is small.
For compatibility reasons the older Quartile function is still supported at the 2016 version of Excel.
We mentioned before that the quartiles reside at the 25, 50 and 75 percent of our population of numbers. What if we want to know the point of the 90% of our dataset?
In this case we use the percentile function. It too offers an inclusive and exclusive version.
In the following example we can see the 90% mark in both computing methods. As we explained before we multiply (n-1) by 0.9 for the inclusive method and (n+1) by 0.9 for the exclusive.
The manual calculation of the percentiles here is a bit more difficult than before but luckily excel saves the day.
Let’s see a more practical example.
We need to calculate the 70th percentile which means the number for the 70% of our population.
As with quartile we will use both methods. First the inclusive one: We will type the name of the function followed by the percentage in question (70%).
Then we do the same for the exclusive version: The function and the percentage.
We notice the difference of the results they produce is small as in the quartile functions.
For compatibility reasons the older Percentile function is still supported at the 2016 version of Excel.
The old Percentile and Quartile functions are the same as their inclusive counterparts. It is preferable to avoid using them because they might not be supported in future versions of Excel.
There are close to 10 different methods for calculating quartiles and percentiles, which provide, most of the time, different results, as we have seen in our examples.
There is no agreed standard way for calculating them, but the exclusive method is the most commonly implemented and used.