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.
Additional Resources
How to Calculate Deciles in Excel
How to Calculate a Five Number Summary in Excel
How to Calculate the Interquartile Range (IQR) in Excel