Table of Contents
Quintiles are a type of statistical measure that divides a data set into five equal parts. To calculate quintiles in Excel, you can use the QUARTILE function. This function takes two arguments: the data set and which quartile you want to calculate. For example, to calculate the first quartile, or the bottom 25%, you would use the argument QUARTILE(data set,1). To calculate the third quartile, or the middle 50%, you would use QUARTILE(data set,3). You can then use this data to create a graphical representation of the data set.
In statistics, quintiles are numbers that split a dataset into five groups of equal frequency.
The first quintile is the point where 20% of all data values lie below it. The second quintile is the point where 40% of all data values lie below it, and so forth.
We can use the following function to calculate the quintiles for a dataset in Excel:
=PERCENTILE(CELL RANGE, QUINTILE)
The following example shows how to use this function in practice.
Example: Calculate Quintiles in Excel
Suppose we have the following dataset with 20 values:
The following image shows how to calculate the quintiles for the dataset:
The way to interpret the quintiles is as follows:
- 20% of all data values lie below 6.8.
- 40% of all data values lie below 14.
- 60% of all data values lie below 20.8.
- 80% of all data values lie below 26.2.
We can also use the following formula to calculate each quintile at the same time:
=PERCENTILE(CELL RANGE, {0.2, 0.4, 0.6, 0.8})
The following image shows how to do so:
Notice that the quintiles calculated here match the quintiles we calculated earlier.