Table of Contents
The Interquartile Range (IQR) in Excel can be calculated by entering the data into a spreadsheet, sorting it in ascending order, and then calculating the difference between the 75th and 25th percentile values. This difference gives you the IQR for the data.
This tutorial explains how to calculate the interquartile range of a dataset in Excel.
What is the Interquartile Range?
The interquartile range, often denoted IQR, is a way to measure the spread of the middle 50% of a dataset. It is calculated as the difference between the first quartile* (Q1) and the third quartile (Q3) of a dataset.
*Quartiles are simply values that split up a dataset into four equal parts.
For example, suppose we have the following dataset:
[58, 66, 71, 73, 74, 77, 78, 82, 84, 85, 88, 88, 88, 90, 90, 92, 92, 94, 96, 98]
The third quartile turns out to be 91 and the first quartile is 75.5. Thus, the interquartile range (IQR) for this dataset is 91 – 75.5 = 15. This tells us how spread out the middle 50% of the values are in this dataset.
How to Calculate the Interquartile Range in Excel
Microsoft Excel doesn’t have a built-in function to calculate the IQR of a dataset, but we can easily find it by using the QUARTILE() function, which takes the following arguments:
QUARTILE(array, quart)
- array: the array of data you’re interested in.
- quart: the quartile you would like to calculate.
Example: Finding IQR in Excel
Suppose we would like to find the IQR for the following dataset:
To find the IQR, we can perform the following steps:
Step 1: Find Q1.
To find the first quartile, we simply type =QUARTILE(A2:A17, 1) into any cell we choose:
Step 2: Find Q3.
To find the third quartile, we type =QUARTILE(A2:A17, 3) into any cell we choose:
Step 3: Find IQR.
To find the interquartile range (IQR), we simply subtract Q1 from Q3:
The IQR turns out to be 39.5 – 23.5 = 16. This tells us how spread out the middle 50% of the values are in this particular dataset.
A Shorter Approach
Note that we could also have found the interquartile range of the dataset in the previous example by using one formula:
=QUARTILE(A2:A17, 3) – QUARTILE(A2:A17, 1)
This would also result in the value 16.
Conclusion
The interquartile range only represents one way of measuring the “spread” of a dataset. Some other ways to measure spread are the range, the standard deviation, and the variance.
The nice part about using the IQR to measure spread is that it’s resistant to outliers. Since it only tells us the spread of the middle 50% of the dataset, it isn’t affect by unusually small or unusually large outliers.
This makes it a preferable way to measure dispersion compared to a metric like the range, which simply tells us the difference between the largest and the smallest values in a dataset.