How to calculate interquartile range in excel? | Easy Formula

In this article, let’s see how you can calculate interquartile range in excel.

What is an interquartile range (IQR)?

IQR represents where does the middle (50%) or bulk of values lie in a given range of data. Let’s simplify it further.

From a given range of data like students’ scores, first it is sorted in order of smallest to largest value such that, the data can be divided into 4 parts or 4 quartiles.

IQR here is the difference between first quartile (25th percentile) & third quartile (75th percentile).

IQR=Q3-Q1

Let’s see an example. Here is a range of values: 1 2 3 4 5 6 7 8 9 10 11

Here first quartile is at 3.5 (Between 3 & 4) and third quartile is 8.5 (Between 8 & 9).

IQR=8.5-3.5
       =5

Calculating Interquartile Range Excel

In excel, there is no direct method or function to calculate interquartile range on excel. So we have to use another way to do so.
One of the easiest method is to calculate Q3 & Q1 and subtract them.

Here are the steps on how to calculate IQR in excel:

  1. Select the cell, where we want to get the value of Q1. Then type =Quartile(array,1).
    Here the array means the range of the cells. Just select the range of cells by dragging the cells. Also, 1 in the formula represents quartile 1, it’s telling excel to return the value of Q1


  2. Now, calculate the value of Q3 the same way. Use the same formula, type =Quartile(array,3). Again array should be the range of cells and 3 is Q3.


  3. Now, select the cell where you want to display the value or IQR in excel. Use the simple subtract formula i.e type=D4-D3. D4 has the value of Q3 & D3 of Q1.


  4. You can use a much faster way. Instead of calculating Q1 & Q3 and then subtracting. You can use 1 single formula to calculate IQR in excel.
    You can directly type, =Quartile(array,3)-Quartile(array,1)
    Just replace array with the range of cells.

Conclusion

IQR is just the measure of middle dispersion of given data, i.e difference between Q3 & Q1.
I hope this article was helpful to you and explained you in depth how to find IQR in excel and what IQR is.

If you have any questions or tips, I’d love to hear them in the comment section below.

This Post Has One Comment

  1. sabrina

    Hello, what if my data have duplicate numbers, should I removed them or just use the formula for the whole data?

Leave a Reply

Advanced Excel Course

Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.

  • 10+Hrs of Learning Content
  • 50+ Practical Exercises
  • Certification
  • Doubt Resolution
Eduolio.com | Copyright © 2021 | All Rights Reserved
Homepage Footer Logo
Eduolio.com | Copyright © 2021 | All Rights Reserved