The Interquartile Range is commonly used in statistics as it helps us identify the natural seasonality and monthly trends in a dataset.
It gives you insight into the spread of your data, making it useful in statistical analysis.
While Excel does not have an inbuilt formula to calculate the interquartile range, it does have the QUARTILE function, which we can use to easily calculate the interquartile range in Excel.
In this tutorial, I will show you how to find the Interquartile Range in Excel using some simple formulas.
Understanding Quartiles
Before I get into the concept of Interquartile range, let me quickly explain the concept of quartiles (which is necessary to calculate the interquartile range)
Quartiles are a useful way to divide your data into four equal parts.
They help you understand the spread and distribution of values in a dataset. In simple terms, a quartile is a value that separates your data into four equal groups.
- First Quartile (Q1): This is the middle value between the smallest number (minimum) and the median of the dataset. It represents the 25th percentile, meaning that 25% of the values in your data are at or below Q1.
- Second Quartile (Q2): This is the median of the dataset, which is the middle value when the data is sorted in ascending order. 50% of the data values are below the median, while the other 50% are above it.
- Third Quartile (Q3): This is the middle value between the median and the largest number (maximum) of the dataset. It represents the 75th percentile, indicating that 75% of the values in your data are at or below Q3.
Now that you understand quartiles, you can easily calculate the Interquartile Range (IQR) in Excel.
The IQR is the difference between the third quartile (Q3) and the first quartile (Q1). It represents the range within which the middle 50% of data points fall, giving you a better understanding of how your data is spread around the median value.
What is an Inter-Quartile Range (IQR)?
The Interquartile Range (IQR) helps you understand how data is spread out.
In simple terms, IQR represents the range where the middle 50% of your data lies. It gives you an idea of how close or far apart your data points are from each other.
It can be used to measure how much a data set varies around its median value. This information can be helpful when trying to identify any unusual spikes or drops in data that may not be due to seasonal or monthly variations.
Calculating the IQR involves identifying the first quartile (Q1) and the third quartile (Q3) of your dataset.
The first quartile represents the value that separates the lowest 25% of the data, and the third quartile represents the value that separates the highest 25%.
Once you have Q1 and Q3, you can find the IQR by subtracting Q1 from Q3:
IQR = Q3 - Q1
The IQR is important because it gives a sense of how “variable” or spread out a set of data is.
One use case for knowing the IQR would be if you were trying to detect seasonality in your data.
You might look at the IQR over different months to see if there was a clear pattern (i.e., whether the data was more spread out in some months than others). This could help you identify which months were more important for your
You can use this measure if you need to overcome sensitivity to outliers in your dataset.
So, let’s get started with a simple example and see how you can find the interquartile range value in Excel.
Also read: Calculate Correlation Coefficient in Excel
Calculating Interquartile Range in Excel (Formula)
Below, we have a dataset with test scores in cell range B2:B21, and we want to calculate the Interquartile Range in cell D3.
Below is the formula that will give us the Interquartile Range:
=QUARTILE.INC(B2:B21,3)-QUARTILE.INC(B2:B21,1)
Let me explain a bit more about how this formula works.
Excel doesn’t have a function that directly calculates the Interquartile Range. Instead, we have to use QUARTILE.INC Function to return the third quartile and the first quartile in a formula.
The difference between the two gives us the Interquartile Range value.
The syntax of the QUARTILE.INC Function is:
=QUARTILE.INC(array, quart)- array – this is a range of cells or an array containing numeric values. This is a required value.
- quart – this is a value between 0 and 4 that indicates what the function should return. If 0 is specified, then the function returns the minimum value. If 1 is specified, then the function returns the first quartile. If 2 is specified, then the function returns the median. If 3 is specified, then the function returns the third quartile. If 4 is specified, then the maximum value is returned. This is a required value.
So, the first part of the formula returns the third quartile, since we have specified the number 3.
The second part of the formula returns the first quartile, since we specified the number 1.
The formula thus calculates the difference between the two. We have an IQR of 6.
Note: The QUARTILE Function in Excel is included for compatibility purposes for older versions of Microsoft Excel. For people using newer versions, rather use QUARTILE.INC or QUARTILE.EXC. QUARTILE.INC is inclusive of the 0 and 1 percentile range, whereas QUARTILE.EXC is exclusive of 0 and 1 in its calculation.
Also read: Calculate MEDIAN IF in Excel
How to Interpret the Result of Inter Quartile Range (IQR)?
IQR can be interpreted in a few different ways, but one way to think about it is that it represents the range of values within which most of the data points lie.
So, if you have a data set with a lot of variabilities, then the IQR will be high compared with when the data set is more clustered around a particular value.
A larger IQR indicates a greater spread in the middle 50% of the data. Conversely, a smaller IQR suggests that the data is more tightly clustered around the median.
Since the IQR focuses on the median and quartiles, it is not influenced by the mean. This is particularly useful in skewed distributions.
Remember, the IQR is a measure of variability, not central tendency. It should be used as part of a broader statistical analysis rather than in isolation to understand the characteristics of a dataset effectively.
What is the 1.5*IQR Rule?
The 1.5 IQR rule is a way of looking at the distribution of your data in order to identify outliers.
You take the interquartile range (IQR), which is the difference between the third quartile and first quartile, and multiply it by 1.5.
Any value that is greater than or equal to the upper quartile plus 1.5IQR and less than or equal to the lower quartile minus 1.5IQR is considered an outlier.
In general, you want to eliminate outliers from your data set because they can throw off your results. However, sometimes you may want to keep them because they can be informative (for example, if you’re studying seasonality)
What is the Best Way to Show Inter Quartile Range in Excel?
To display the interquartile range visually in Excel, create a box plot.
A box plot is a type of chart that shows the distribution of data sets through their quartiles.
Other Excel articles you may also like:
