How to Calculate the Interquartile Range in Excel

This guide will explain how to calculate the interquartile range in Excel.

The interquartile range is a measure of dispersion. We can find this range by calculating the difference between the lower and upper quartiles of our sample. 

How do we define the interquartile range? We can divide our dataset into two halves based on the median. The median of the upper half is considered the upper quartile. Meanwhile, the median of the lower half will be our lower quartile.

This measure can help you find the spread of your dataset. The benefit of calculating the interquartile range is that it is not affected by outliers. Outliers may skew the data and provide an inaccurate summary of the entire population.

Let’s take a look at a sample scenario where we can benefit from calculating the interquartile range.

Suppose you have a dataset of patient heights. You want to determine the difference between the spread of male and female patients.

We can use the interquartile range to measure the spread of the middle half of our data. We can use this measurement along with other metrics, such as the median, to form a better analysis of our sample populations.

The QUARTILE function in Excel will be used to return the first and third quartiles. We’ll compute the difference between these two values to find the interquartile range for both the male and female samples.

Now that we know that we can use the interquartile range to measure the spread of data, let’s explore an actual sample spreadsheet.

 

 

A Real Example of Calculating the Interquartile Range in Excel

The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a look at the sample dataset we’ll need to analyze.

Our table consists of a random sample of 30 patients. Each patient has a recorded height measured in centimeters.

sample data of patient heights

 

We’ll use the QUARTILE function to return the range’s 1st and 3rd Quartile. These values are equivalent to the 25th and 75th percentile, respectively.

calculate the interquartile range in Excel

 

To get the 1st QUARTILE in Column C, we just need to use the following formula:

=QUARTILE(B2:B31,1)

We can replace 1 with 3 to return the 3rd quartile instead.

Microsoft Excel comes with an alternative quartile function called .

We can use the QUARTILE.EXC function if we want to compute for a wider interquartile range with fewer outliers.

find inclusive and exclusive interquartile range in excel

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to calculate the IQR yourself, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Calculate the Interquartile Range in Excel

This section will guide you through each step needed to calculate the interquartile range in Excel. You’ll learn how to use the QUARTILE function to determine the 1st and 3rd quartile of a given sample population. We’ll find the difference between these values to find the interquartile range.

Follow these steps to calculate the interquartile range in Excel:

  1. First, select the cell that will compute the first quartile of the given population. In this example, we’ll place the QUARTILE function in cell E2.
    select first quartile
  2. Next, type in the QUARTILE function in the formula bar. The first argument is a cell reference to the range that holds the sample population. We’ll input ‘1’ as our second argument to indicate that we want to return the first quartile or 25th percentile.
    calculate the first quartile
    Hit the Enter key to evaluate the function.
  3. Select the cell that will compute the third quartile of the given population. In this sheet, w will add the second QUARTILE function in cell E3.
    select another cell to return thrid quartile
  4. Type ‘3’ into the second argument of the QUARTILE function to indicate that we want to return the 3rd quartile.
    find third quartile
  5. Next, we’ll select another cell and write a formula to find the difference between the first and third quartiles. In this example, we’ll find the difference by subtracting E2 from E3.
    subtract values of first and third quartile
  6. Hit the Enter key to return the final result.
    calculate the interquartile range in Excel

These are all the steps you need to calculate the interquartile range in Excel. 

 

 

This step-by-step guide should provide you with all the information you need to calculate the interquartile range in Excel.

The interquartile range is a useful metric that tells you how to spread out your data is at a glance.

The quartile function is just one example of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

For example, you can read our article on univariate analysis in Excel to learn more about other summary statistics you can derive with Excel functions.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like