Skip to content

How to find IQR (Interquartile Range) in Excel?

  • by
  • 4 min read

Interquartile range (IQR) is the interval between the first (25th percentile) and the third quartiles (75th percentile) in a series of numbers arranged in ascending order.

When subtracting Q1 from Q3, you’ll get a range of middle values. It explains whether the data is dispersed or clustered around the median. IQR also helps to determine the outliers in a given set of data. Outliers are those values that are extremely high or low compared to other values.

Furthermore, you can find the seasonality and trends in your data using IQR. Seasonality refers to the patterns and changes you usually calculate for a fixed period, such as monthly, quarterly, half-yearly or yearly. The trend is a general direction of the data over long periods that explains whether the data is pointing upwards, downwards, or stagnant.

In this article, we’ll explain how to find IQR in Excel easily. You’ll be surprised that you cannot find IQR in Excel directly, as there is no formula. However, you can do it in parts like finding the quartiles, calculating the IQR, or directly subtracting the quartile formulas.


Finding the quartiles

First, let us begin by finding the quartiles. I have taken the following data for demonstration: 12, 18, 22, 25, 30, 35, 40, 45, 50, 55, 60. As you can see,, the numbers are arranged in ascending order, starting from the lowest value and heading towards the highest value.

To find the quartiles, you must use the formula: =QUARTILE.INC(array, quart) where array represents the whole data set and quart represents the quartile. For example, if you want to find the first quartile, you will enter 1. Or, to find the third quartile, enter 3.

So, to find the first quartile, the formula should look something like this: =QUARTILE.INC(array, 1). Take a look at the screenshot above. The array range is from cell A1 to A11, which you see in the formula. When you’re done, close the brackets and press enter.

You’ll see the Q1 in the cell. Similarly, use the formula to find the Q3. Just substitute 3 in place of 1. The formula to calculate Q3 for the above numbers arranged in cells A1 to A11 will look like this: =QUARTILE.INC(A1:A11, 3).

So, the Q1 came out to be 23.5, and Q3 is 47.5.

Also read: How to find external links in Excel?


Finding the IQR

As explained earlier, you can calculate IQR by subtracting Q1 from Q3. You can do this manually or use the subtraction formula in Excel. The formula will be: -=(CellQ3-CellQ1), where CellQ3 is the cell where the value of the third quartile is stored, and CellQ1 is the cell with Q1 value.

In the above case, the Q3 value is in cell B12, and Q1 is stored in cell B11. So, the formula will be: =B12-B11.

Finally, we get the answer: 24.

A more direct method would be simply subtracting the two formulas we discussed in the previous section. This might make the work easier for you. Here’s the formula: =QUARTILE.INC(array, 3) – QUARTLE.INC(array, 1).

Here’s what you will get after you have pressed Enter:

In conclusion, finding IQR in Excel is easy and convenient and just requires a few clicks. You can find other values and trends for your business or company using IQR.

Also read: How to enable and disable Excel autosave?

Kumar Hemant

Deputy Editor at Candid.Technology. Hemant writes at the intersection of tech and culture and has a keen interest in science, social issues and international relations. You can contact him here: kumarhemant@pm.me

>
Exit mobile version