The interquartile range is a useful statistical measure which provides the range over which the middle 50% of values in a data set reside. This range of values is often represented graphically in a box and whisker plot which shows where the interquartile range is shown within the box part of the plot an example of this is shown in the image below.
The values of each and every point within the box plot, including the interquartile range which is the length of the box can be calculated quickly and easily using formulas within Excel.
Calculating The Interquartile Range
Any data set can be divided into quartiles or quarters. The Lowest valued numbers are referred to as the first quartile and represent the bottom 25% of values within a dataset, an example of this is shown in the image below
The value of each and every line can be calculated using the quartile function within Excel which has the the structure,
Array is the dataset;
Quart is the quartile the number is being calculated. If the argument is 0 the value displayed will be the minimum value in the data set and 4 will give the maximum value in the data set. Arguments 1, 2, and3 will give the top value in the respective quartiles.
It is important to note that if there is an even number of digits in the overall dataset the quartile will be calculated on the average of the values on either side of the quarter border. Calculate the interquartile range of the dataset you need to calculate the difference between Q3 and Q1. The formula required to do this is provided below;
=quartile(A:A,3) – quartile(A:A,1)
What $ Does In Excel Formulas? How To Use Them And Handy Shortcuts
Why Is My Vlookup Returning Wrong Value? How To Fix The Problem
How To Remove Duplicates In Excel Using Vlookup
How To Apply An If Statement If The Cell Only Contains A Partial Match To Text (With And Without VBA)