Syntax:=QUARTILE(data_range, quartile_number)
data_range
– It represents the range of cells which contain the cells of which the interquartile range is to be calculatedquartile_number
– It represents the quartile which is to be calculated.
Sample usage:=QUARTILE(B4:B15,3)
// It calculates 3rd quartile for the range of data in B4:B15
Sample Google Sheets template with formula here.
The Inter-Quartile Range (IQR) represents the difference between the third quartile and the first quartile of the given data. It can also be calculated by subtracting the 75th percentile and the 25th percentile. The Interquartile Range is an effective statistic which can be used to measure the variability in the data when either the data is skewed or it contains outliers, i.e., extreme values.
A dataset may contain extreme values or be skewed in a direction when its values are seasonal in nature. For e.g. a dataset that shows the number of umbrellas sold in each month in a year will indicate a higher number of umbrellas sold in the rainy seasons as compared to the winter season.
Let’s take the example forward and calculate the interquartile range in Google Sheets for the number of umbrellas sold by a firm in each month in a year using the data below.

To calculate the interquartile range in Google Sheets, we can use either of the following two methods:
Method #1: Using the QUARTILE Function
Quartiles are the list of three points that divide the data into four equal parts. As discussed above, the interquartile range is the difference between the third and the first quartile of the given data.
Calculate the Interquartile range in Google Sheets using the QUARTILE function
Now that we have familiarised ourselves with the QUARTILE function and its formula, we can use it to calculate the first and third quartiles using the formula below.
→ For the first quartile, we input the following formula in a blank cell (H4) as shown below.
=QUARTILE(B4:B15,3)

→ For the third quartile, we input the following formula in another blank cell (H5):
=QUARTILE(B4:B15,1)

The first and the third quartile represents the values under which 25% and 75% of data points are found when the whole set is arranged in increasing order.
Now that we have the two quartiles, all we need to do is to subtract them to calculate the interquartile range in Google Sheets as shown below:

Method #2: Using the PERCENTILE Function
The kth percentile represents the data point below which k percentage of data exists when data is arranged in increasing order. For instance, the 25the percentile indicates that the value calculated is greater than 25% of the total data points.
When using percentile to calculate the interquartile range all, one needs to do is find the difference between the 75th and 25th percentile, respectively.
Formula for the PERCENTILE function
The formula for the PERCENTILE function in Google Sheets is as follows:
=PERCENTILE(data_range, percentile)
The PERCENTILE function takes two compulsory arguments:
- data_range: It represents the range of cells which contain the cells of which the interquartile range is to be calculated
- percentile: It represents the percentile which is to be calculated followed by the ‘%’ sign.
Calculate the Interquartile range in Google Sheets using the PERCENTILE function
Now that we have familiarised ourselves with the PERCENTILE function and its formula, we can use it to calculate the 25th and 75th percentile using the formula below.
→ For the 75th percentile, we input the following formula in a blank cell (H10) as shown below.
=PERCENTILE(B4:B15,75%)

→ For the 25th percentile, we input the following formula in a blank cell (H11) as shown below.
=PERCENTILE(B4:B15,25%)

Lastly, to calculate the interquartile range in Google Sheets using the PERCENTILE function we calculate the difference between the 75th and the 25th percentile, respectively, as shown below.

We can prove that we have found the right answer as the range obtained using both methods is the same.
Conclusion
Since the interquartile range is indifferent to the presence of outliers, it provides a more authentic result when compared to other metrics such as the range that only provides the difference between the largest and the smallest values in the dataset.
To know more about the different statistical measures that one can calculate using Google Sheets, head to our blog.
See Also
Refer to the list of articles below which you may find useful.
Standard deviation in Google Sheets – 3 Min Easy Guide