Calculate Interquartile Range (IQR) in Excel

Interquartile Range (IQR) is often used in statistics to identify outliers and understand the spread of the middle 50% of the data set.

Interquartile Range allows you to analyze data without it getting skewed by outliers and extreme values. This helps us get a better view of the variability of the data as compared to other similar metrics, such as range or standard deviation.

While there is no dedicated formula in Excel to calculate the interquartile range, there is a formula to calculate Quartiles, which can easily be used to calculate the IQR.

Before I show you how to calculate the Interquartile Range in Excel, let me quickly explain the concept of quartiles and how we can use them to calculate IQR.

What are Quartiles and InterQuartile Range?

In statistics, a population or a dataset can be divided into four parts, and each part is called a quartile (called Q1, Q2, Q3, and Q4).

For the sake of explanation, let’s say I have a series of numbers as shown below in ascending order. Let’s assume that these are scores of students in a test.

0, 1, 2, 3, 4, 5, 6, 7 8, 9, 10

If I calculate the first quartile from this range, it will give me a number below which the first 25 percentile of the scores lies.

In this range, that value would be 2.5 (which means that if you score 2.5 or below, then you would be at the bottom 25% according to this dataset)

Similarly, Quartile 2 would be 5, Quartile 3 would be 7.5, and Quartile 4 would be 10.

Now, once you have the Quartile values, you can calculate the interquartile range by using the below formula:

IQR = Quartile 3 - Quartile 1

So, in our example, the IQR would be:

IQR = 7.5 - 2.5

Note: I have shown you a series of numbers in ascending order just for simplicity to explain the concept of IQR. In reality, these numbers could be anything and may not be in an ascending or descending order (as shown in the example next).

Also read: How to Calculate PERCENTILE in Excel

Calculating Interquartile Range (IQR) in Excel

Now, let’s see how to calculate IQR in Excel using in-built formulas.

As I mentioned, Excel does not have an inbuilt function to calculate the Interquartile Range. However, it does have the Quartile function that we can use for this purpose.

Below is a dataset of scores of students in a class, and I want to calculate the IQR for this dataset.

To calculate the Interquartile Range, we would first have to calculate Quartile 1 and Quartile 3 values.

Enter the below formula in cell E2 to get the Quartile 1 Value:

=QUARTILE.INC(B2:B16,1)

The QUARTILE.INC function takes two arguments – the range that has the numbers and the Quart value we want to get. In this case, since we wanted the first-quart value, we used 1 as the second argument.

Now, enter the below formula in cell E3 to get the Quartile 2 Value:

=QUARTILE.INC(B2:B16,3)

Now use the below formula in cell E4 to get the IQR value:

=E3-E2

If you don’t want to do it in parts, you can get the IQR directly with this single formula:

=QUARTILE.INC(B2:B16,3)-QUARTILE.INC(B2:B16,1)

So, this is a simple formula you can use to calculate IQR in Excel.

Also read: How to Find Range in Excel

How to Interprent Interquartile Range Value?

The IQR tells us how spread out the middle half of your data is (which is the range between the 25th and the 75th percentile value).

A high IQR value indicates a greater spread of the middle data points, while a lower IQR value suggests that these points are closer together.

So, if I take an example of scores of students in a class, a low IQR value tells me that students have scored consistently, and there is not a huge difference in the scores of students in the bottom 25 percentile and top 25 percentile.

On the other hand, if the IQR value is high, it tells me that the variability is high, and there is a large difference between the scores of students who scored in the bottom 25 percentile and the ones who scored in the top 25 percentile.

Since the IQR uses quartiles and not mean/average, it is a better measure to learn about the variability of datasets (especially when you have skewed distributions).

In Excel, IQR is best represented by using Box Plots charts (as it shows the data distributions based on the quartiles)

Also read: How to Make a Bell Curve in Excel

IQR*1.5 Rule to Find Outliers

There is a 1.5*IQR rule that helps you identify outliers in your dataset.

This is quite useful in statistics when you have large datasets and you want a basis to identify outliers.

According to this rule, outliers are those values that fall below Q1 – 1.5 * IQR or above Q3 + 1.5 * IQR

So, this is how you can use a simple formula to calculate the Interquartile Range (IQR) in Microsoft Excel.

I hope you found this Excel article useful

Other Excel articles you may also like: