The interquartile range, or IQR, represents the spread of the middle 50% of the dataset. In particular, the interquartile range represents the difference between the first quartile and the third quartile. In this tutorial, you’ll learn how to use Excel to calculate the interquartile range. Excel makes this easy by using the QUARTILE() function, which returns the value of the provided quartile of a given range of values.
By the end of this tutorial, you’ll have learned:
- What is meant by the interquartile range and why you may want to calculate it
- How to use the Excel QUARTILE() function
- How to calculate the interquartile range in Excel
Understanding the Interquartile Range
What is the interquartile range (IQR)?
The interquartile range (IQR) represents the spread of the middle 50% of the distribution of your data. In this case, quartiles segment your ordered data into four equal parts. Because of this, the IQR represents the difference between the third and first quartiles.
Understanding the interquartile range is more easily done with an example. Say that we have a set of student text scores:
30, 38, 41, 49, 49, 58, 61, 62, 68, 68, 70, 71, 75, 76, 78, 80, 84, 89, 92, 95In the dataset above, the data are sorted from smallest to largest. What we can then do, is split the dataset into four chunks, known as quartiles. When we do this, we can identify that the quartiles are:
- Quartile 1: 55.75
- Quartile 2: 69
- Quartile 3: 78.5
The first quartile represents the bottom 25% of the data, while the third quartile represents the top 25% percent of the data.
From here, we can calculate the interquartile range by calculating the difference between the third and first quartile. Let’s see what this looks like:
IQR = Q3 - Q1In this case, this would represent 78.5 - 55.75 = 22.75.
How to Calculate the Interquartile Range in Excel
Excel makes it easy to calculate the interquartile range by making use of the QUARTILE() function. Let’s take a look at what the function looks like:
=QUARTILE(array, quart)We can see that the function accepts two arguments:
array=represents the array of data, such as an Excel rangequart=represents the quartile we want to calculate and accepts values from 0 through 4
How can you calculate the interquartile range in Excel?
To calculate the interquartile range in Excel, you can simply use the QUARTILE() function to calculate: =QUARTILE(values, 3) – QUARTILE(values, 1).
Let’s see what this looks like in Excel. We’ll load our dataset of student scores into column A.
We can calculate the value of the first quartile by using the QUARTILE() function and passing in 1 as the second argument.
We can calculate the remaining quartiles by using the same function and passing in 2 and 3, respectively, for the second and third quartiles.
From here, we’ll enter the formula =QUARTILE(A2:A21, 3) - QUARTILE(A2:A21, 1) into cell C3.
When we hit Return Excel returns the interquartile range value.
We can see that this returned a value of 13.
Understanding Excel’s Quartile Functions
Excel provides three different functions for calculating quartiles. In this tutorial, we used the QUARTILE() function. However, if you want to learn more about the differences between the three functions, check out my tutorial here.
Conclusion
In this tutorial, you learned how to use Excel to calculate the interquartile range. You first learned what quartiles are and what the interquartile range represents. From there, you learned how to use the QUARTILE() function in Excel to calculate quartiles. Using this function, you then learned how to calculate the interquartile range.
Additional Resources
To learn more about related topics, check out the tutorials below: