The interquartile range (IQR) is a measure of the dispersion of a dataset. It is calculated as the difference between the 75th percentile (Q3) and the 25th percentile (Q1) of the data and is a way to identify the spread of the middle 50% of the data. In other words, the IQR measures the range of the middle 50% of the data.
To find the IQR of a dataset manually, you first need to arrange the data in ascending order. For example, the data 6, 10, 11, 9, 11, and 14 would be arranged as follows: 6, 9, 10, 11, 11, 14.
Next, you would need to divide the data into quarters. To do this, you would need to find the median of the dataset, which is the middle value when the data is arranged in order. In this case, the median is 10.5, the average of the third and fourth values in the ordered dataset.
The first quarter of the data consists of values less than the median, which in this case are 6, 9 and 10. The third quarter of the data consists of values greater than the median, which in this case are 11, 11 and 14.
The first quartile (Q1) is the median of 6, 9 and 10. This comes out to be 9.
The third quartile (Q3) is the median of 11, 11 and 14. This comes out to be 11.
The interquartile range, in this case, will be Q3 - Q1 or 11 - 9 = 2.
IQR Calculation using Excel:
In Excel, there is no direct way to calculate the Interquartile range. The most common approach is to calculate the third and first quartiles using the quartile function in Excel and then find out the difference (Q3-Q1) between them.
For calculating quartiles, Excel offers two measurements: QUARTILE.INC and QUARTILE.EXC.
To find out the IQR for 6, 9, 10, 11, 11 and 14, put these values in cells A1 to A6 and then use the following formula:
=QUARTILE.EXC(A1:A6, 3) - QUARTILE.EXC(A1:A6, 1)
The above formula returns the IQR as 3.5.
=QUARTILE.INC(A1:A6, 3) - QUARTILE.INC(A1:A6, 1)
The above formula returns the IQR as 1.75.
As you would have noticed, these values are different from the manually calculated value of IQR as 2.0. In the background, Excel uses the PERCENTILE function to calculate QUARTILE. We have a separate post explaining these two functions, PERCENTILE and QUARTILE, in more detail, showing the way these calculations are performed internally. Click here to read.
Advantage of IQR over Range
The IQR is a useful measure of dispersion because it is not sensitive to the data's extreme values or outliers. Because it is based on the middle 50% of the data, the IQR is unaffected by any values that are particularly high or low compared to the rest of the data. This makes it a more robust measure of dispersion than the range, which is sensitive to outliers.