Finding quartiles and the interquartile range is a great way of analyzing numerical data. If you want to calculate the interquartile range in Excel, you’re at the right place.
In this tutorial, we will learn what are quartiles, what is the interquartile range, and how to calculate it in Excel.
What are Quartiles?
Quartiles are considered as the quarters of the data, and therefore there are four of them for each numerical dataset. The quartiles are usually named Q1, Q2, Q3, and Q4. The smallest quarter of values is in quartile Q1, and the highest quarter of values lies in quartile Q4.
Quartiles of your data can be calculated in Excel using the QUARTILE function. The QUARTILE function accepts a range of cells or values (range_of_cells), and a number between 1-4 (N), signifying which quartile to calculate.
=QUARTILE(range_of_cells, N)
Below is a spreadsheet that contains the prices of products, and the four quartiles are computed with the below formula, where N takes the values 1, 2, 3, and 4.
=QUARTILE(A2:A15, 1)
=QUARTILE(A2:A15, 2)
=QUARTILE(A2:A15, 3)
=QUARTILE(A2:A15, 4)
What is the Interquartile Range?
The interquartile range is the middle half (50%) of the data, which means that it is the range between the Q3 value and the Q1 value. Therefore, the interquartile range formula is:
Interquartile Range = Q3 – Q1
Now that we know how to calculate the interquartile range, we can calculate it in Excel.
How to Calculate the Interquartile Range?
To calculate the interquartile range, we need to subtract Q1 from Q3. We already learned that we can use the QUARTILE function to calculate these quartile values:
Q1 = QUARTILE(range_of_cells, 1)
Q3 = QUARTILE(range_of_cells, 3)
When we find these values, we can subtract them, and we can easily obtain the interquartile range:
Interquartile Range = QUARTILE(range_of_cells, 3) – QUARTILE(range_of_cells, 1)
In the product price data, the values lie within the range A2:A15. Therefore, the formula becomes:
=QUARTILE(A2:A15, 3) – QUARTILE(A2:A15, 1)
You can adapt this formula to your data by simply changing the cell range.
If you’ve already calculated Q1 and Q3, you can also subtract these two cells to obtain the interquartile range. The Q1 value is within cell D7, and the Q3 value is within cell D9. So the interquartile range can be calculated with:
= D9 – D7
We learned how to calculate the quartiles and the interquartile range in Excel. The interquartile range is very useful when you want to find out how spread out the middle 50% of your data is. Hope this tutorial helped you better analyze your data!