Table of Contents
Deciles in Google Sheets can be calculated using the QUARTILE function. This function returns the quartile (25th, 50th, 75th percentiles) of a given dataset. For example, to calculate the deciles (10th, 20th, 30th percentiles) of a dataset, you would use the QUARTILE formula with the k-value of 0.1, 0.2 and 0.3 respectively. This formula can be used to quickly calculate the deciles of a dataset in Google Sheets.
In statistics, deciles are numbers that split a dataset into ten groups of equal frequency.
The first decile is the point where 10% of all data values lie below it.
The second decile is the point where 20% of all data values lie below it.
The third decile is the point where 30% of all data values lie below it.
And so on.
We can use the following function to calculate the deciles for a dataset in Google Sheets:
=PERCENTILE(CELL RANGE, PERCENTILE)
For example, we would use the following formula to calculate the value of the third decile for a dataset in the range A1:A50:
=PERCENTILE(A1:A50, 0.3)
The following example shows how to use this function in practice.
Example: Calculate Deciles in Google Sheets
Suppose we have the following dataset with 20 values:
The following image shows how to calculate the deciles for the dataset:
Here is how to interpret each decile value:
- 10% of all data values lie below 63.4.
- 20% of all data values lie below 67.8.
- 30% of all data values lie below 76.5.
To place each data value into a decile, we can use the PERCENTRANK.EXC() function, which uses the following syntax:
=PERCENTRANK.EXC(CELL RANGE, DATA VALUE, SIGNIFICANCE)
The following image shows how to use this function for our dataset:
Note that this function finds the relative rank of a value in a dataset as a percentage and rounds to one digit, which is equivalent to finding the decile that the value falls in.
The way to interpret the output is as follows:
- The data value 58 falls between the percentile 0 and 0.1, thus it falls in the first decile.
- The data value 64 falls between the percentile 0.1 and 0.2, thus it falls in the second decile.
- The data value 67 falls between the percentile 0.1 and 0.2, thus it falls in the second decile.
- The data value 68 falls between the percentile 0.2 and 0.3, thus it falls in the third decile.
And so on.