# How to Calculate Interquartile Range in Excel (2 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Calculating the Interquartile range is an important task as it can be used to find outliers. If you are finding ways how to calculate interquartile range in Excel, this is the right place for you. Here, in this article, you will find step-by-step ways to calculate the interquartile range in Excel.

## What Are Quartiles?

Quartiles are statistical values that divide the data into four equal parts. To divide the data into quartiles first sort the numbers in ascending order. Then it is cut into four parts.

The 25th percentile is called the First Quartile (Q1), the 50th percentile is known as the Second Quartile (Q2) or Median, 75th percentile is the Third Quartile (Q3).

Example: 1, 1, 7, 3, 6, 4, 5, 6, 3, 6, 2
Ascending Order: 1, 1, 2, 3, 3, 4, 5, 6, 6, 6,7

Here, Q1 = 2     Q2/ Median = 4       Q3 = 6

## What Is an Interquartile Range (IQR)?

Interquartile Range (IQR) represents the middle 50% value of ordered data. It is the difference between the Third Quartile(Q3) and First Quartile(Q1).

Equation:   IQR = Q3-Q1

For the example given above, Interquartile Range (IQR) = 6 2 = 4

Here, you will find ways to calculate the Interquartile Range (IQR) of a dataset in Excel. Go through the steps and calculate the IQR for your own dataset. Here, we have a dataset containing the Scores of some students. We will calculate the Interquartile Range (IQR) of this data using the QUARTILE function. ## 1. Using QUARTILE Function to Calculate Interquartile Range in Excel

We can calculate Interquartile Range in Excel using the QUARTILE function. This function can calculate IQR in Excel by calculating Q1 and Q3 or else by using a direct equation.

For the first method, we will use the QUARTILE function to calculate the value of the Interquartile Range (IQR). Follow the steps to calculate the value of IQR for your own dataset.

Steps:

• To begin with, select Cell F6.
• Then, type the following formula.
`=QUARTILE(C5:C15,1)` Here, in the QUARTILE function, we selected the range C5:C15 as an array and gave 1 as quart where 1 means 25th percentile. Now, it will return the first quartile from the given array.

• Now, press ENTER to get the value of Quartile(Q1). • After that, select Cell F7.
• Then, type the following formula.
`=QUARTILE(C5:C15,3)` Here, in the QUARTILE function, we selected the range C5:C15 as an array and gave 3 as a quart where 3 represents the 75th percentile. So, it will return the third quartile from the given array.

• Now, press ENTER to get the value of Quartile(Q3). • Then, to calculate the value of the Interquartile Range (IQR) find the difference between Quartile(Q1) and Quartile(Q3). Type the following formula in cell F8.
`=F7-F6` • Press ENTER to get the value of the Interquartile Range (IQR). This is how you can calculate the value of the Interquartile Range (IQR) in Excel by using the QUARTILE function.

## 2. Inserting QUARTILE.INC Function to Calculate Interquartile Range in Excel

For the second method, we will calculate the Interquartile Range(IQR) by using the QUARTILE.INC function. Here, it includes the values from 0 to 1. Steps:

• To begin with, select Cell C17.
• Then, type the following formula.
`=QUARTILE.INC(C5:C15,3)-QUARTILE.INC(C5:C15,1)` Here, in the QUARTILE.INC function, we selected the range C5:C15 as an array. Then to subtract Q1 from Q3 we gave 3 as a quart where 3 means 75th percentile in the first part of the equation and gave 1 as a quart where 1 represents the 25th percentile in the second part of the equation.

• Now, press ENTER to get the value of the Interquartile Range (IQR). This is how you can calculate the value of the Interquartile Range (IQR) in Excel by directly using the QUARTILE.INC function.

## Things to Remember

• Here quart = 0, 2, 4 refers to MIN, MEDIAN, and MAX. You can also use these values in the QUARTILE function
• Whenever the array is empty It will show #NUM! error.
• The #NUM! error is also shown when quart<0 or quart>4.

## Practice Section

You will find an Excel Sheet like this in this article. Practice on your own using these functions to calculate the Interquartile Range (IQR) of this dataset. ## Conclusion

So, in this article, you will find ways to calculate the interquartile range (IQR) in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  