How to calculate interquartile range in excel? | Easy Formula
In this article, let’s see how you can calculate interquartile range in excel.
Table of Contents
What is an interquartile range (IQR)?
IQR represents where does the middle (50%) or bulk of values lie in a given range of data. Let’s simplify it further.
From a given range of data like students’ scores, first it is sorted in order of smallest to largest value such that, the data can be divided into 4 parts or 4 quartiles.
IQR here is the difference between first quartile (25th percentile) & third quartile (75th percentile).
IQR=Q3-Q1
Let’s see an example. Here is a range of values: 1 2 3 4 5 6 7 8 9 10 11
Here first quartile is at 3.5 (Between 3 & 4) and third quartile is 8.5 (Between 8 & 9).
IQR=8.5-3.5
=5
Calculating Interquartile Range Excel
In excel, there is no direct method or function to calculate interquartile range on excel. So we have to use another way to do so.
One of the easiest method is to calculate Q3 & Q1 and subtract them.
Here are the steps on how to calculate IQR in excel:
- Select the cell, where we want to get the value of Q1. Then type =Quartile(array,1).
Here the array means the range of the cells. Just select the range of cells by dragging the cells. Also, 1 in the formula represents quartile 1, it’s telling excel to return the value of Q1 - Now, calculate the value of Q3 the same way. Use the same formula, type =Quartile(array,3). Again array should be the range of cells and 3 is Q3.
- Now, select the cell where you want to display the value or IQR in excel. Use the simple subtract formula i.e type=D4-D3. D4 has the value of Q3 & D3 of Q1.
- You can use a much faster way. Instead of calculating Q1 & Q3 and then subtracting. You can use 1 single formula to calculate IQR in excel.
You can directly type, =Quartile(array,3)-Quartile(array,1)
Just replace array with the range of cells.
Conclusion
IQR is just the measure of middle dispersion of given data, i.e difference between Q3 & Q1.
I hope this article was helpful to you and explained you in depth how to find IQR in excel and what IQR is.
If you have any questions or tips, I’d love to hear them in the comment section below.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Certification
- Doubt Resolution
This Post Has One Comment
Hello, what if my data have duplicate numbers, should I removed them or just use the formula for the whole data?