How To Calculate The Interquartile Range in Google Sheets

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 calculated

quartile_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.

• Method #1: QUARTILE Function
• Method #2: PERCENTILE Function

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 75^th percentile and the 25^th 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 k^th percentile represents the data point below which k percentage of data exists when data is arranged in increasing order. For instance, the 25^the 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  75^th and 25^th 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 25^th and 75^th percentile using the formula below.

→ For the 75^th percentile, we input the following formula in a blank cell (H10) as shown below.

=PERCENTILE(B4:B15,75%)

→ For the 25^th 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 75^th and the 25^th 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

Find mean, median and mode in Google Sheets

Simple moving average in Google Sheets