The Interquartile Range is commonly used in statistics as it helps us identify the natural seasonality and monthly trends in a dataset.
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, we will cover how to calculate the Interquartile Range in Excel.
What is Inter-Quartile Range (IQR)?
The Interquartile Range or IQR is used to measure spread and denotes the middle (50%) spread of one’s dataset.
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.
It’s calculated by subtracting the first quartile (the 25th percentile) from the third quartile (the 75th percentile).
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.
How to Get the Interquartile Range Using the QUARTILE.INC Function
Below we have a dataset with test scores in cell range B2:B21, and we want to calculate the Interquartile Range in 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, 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.
How to Interpret the Result of 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.
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)
Other Excel articles you may also like:
