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

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.


Download Practice Workbook


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


2 Ways to Calculate Interquartile Range in Excel

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.

How to Calculate Interquartile Range in Excel


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).

How to Calculate Interquartile Range in Excel Using QUARTILE Function

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).

How to Calculate Interquartile Range in Excel Using QUARTILE Function

  • 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).

How to Calculate Interquartile Range in Excel Using QUARTILE Function

  • 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).

How to Calculate Interquartile Range in Excel Using QUARTILE Function

This is how you can calculate the value of the Interquartile Range (IQR) in Excel by using the QUARTILE function.


2. Using 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.

How to Calculate Interquartile Range in Excel Using QUARTILE.INC Function

Follow the steps below to do it on your own.

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).

How to Calculate Interquartile Range in Excel Using QUARTILE.INC Function

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.

Practice Section


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. And, visit ExcelDemy for many more articles like this. Thank you!

Arin
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo