Stats students must know about the term Interquartile Range which helps in recognizing the natural flow that appears in a dataset. In Excel, you will not find any direct way to analyze the IQR, however, you still need an answer to how to calculate IQR in Excel.
What is the IQR?
Before getting started let’s understand what this IQR term means in reality. When you tend to find the difference between the first and third quartiles it usually gives IQR value. moreover, the IQR shows 50% of the data. You can simply sort data in four sections when you need a dataset to be sorted from the smallest to the largest values. Below is a simple example you can consider:
Q1 is 3.5 and Q3 is 8.5
The IQR would be 5:
How to Calculate IQR in Excel with Quartile Function?
With the help of the Quartile function, you can easily find out the Interquartile Range in Excel. Using this function, you need to calculate Q1 and Q3 to find IQR in Excel and for this, you need to follow the steps given steps:
- Choose Cell F6 in the first step.
- Enter the following formula.
- =QUARTILE(C5:C15, 1)
- The range C5:C15 is selected in the QUARTILE function and it returns 1 as quart which simply means 25th percentile. Now, you will get the first quartile from the array.
- Press ENTER key and you will get the value of Quartile(Q1).
- Choose cell F7.
- Enter the following formula.
=QUARTILE(C5:C15, 3)
- In this function, the C5:C15 is selected as an array and it returns 3 as a quart in which 3 shows the 75th percentile. You will get the third quartile in return from the array.
- Press ENTER key and you will get the value of Quartile(Q3).
- Now, you need to find the difference between Quartile Q1 and Quartile Q3. For this, enter the following formula in cell F8:
=F7-F6
- Press ENTER key and you will get the Interquartile Range.
That’s it!
This is how to calculate IQR in Excel with the help of the QUARTILE function.
How to Calculate IQR in Excel with the QUARTILE.INC Function?
In this method, you will find an example in which the dataset has test scores given in cell range B2:B21. You need to find the Interquartile Range in the D3 cell.
Here is the formula given:
=QUARTILE.INC(B2:B21,3)-QUARTILE.INC(B2:B21,1)
Let’s have a bit more understanding of how this formula works:
As already explained that Excel does not have a direct function to calculate the IQR, that’s why some alternatives appear including the use of QUARTILE.INC function. It helps in returning the third quartile and the first quartile in the formula.
When you get the value of the difference between these two, it is called the Interquartile Range value. and the syntax for this function is given below:
Syntax of the QUARTILE.INC Function:
=QUARTILE.INC(array, quart)
array: This argument contains the cell range or numeric values. And this is a compulsory value to be added to the formula.
quart: This is also a required value that gives a value between 0 and 4. It simply indicates the function should return. You will always get a minimum value when 0 is specified. On the other hand, you will get the first quartile when 1 is specified. In case, when 2 is specified, you will get the median value.
Ultimately, you have noticed that the first part of the formula is given the third quartile, whereas we have specified the number 3. When the number 1 is specified, the second part of the formula gives the first quartile. And we have an IQR of 6 that appears by calculating the difference between these two values.
How Do You Interpret the IQR Results?
You can interpret the IQR results in several different ways. You can simply take it as a value that shows the value range in which you can see the most data points. Therefore, you must know that the IQR value will be higher when the dataset has multiple variables.
Things to Consider:
MIN, MEDIAN, and MAX values can be used in the QUARTILE function.
You will face the #NUM! error when the array does not have any value.
Whenever you will have the quart<0 or quart>4, you will face the #NUM! error.
Summary:
In this post, you have learned how to calculate IQR in Excel with the help of multiple approaches. Each method is worth appraising and you can try any of them without risk.
