Interquartile Range in Business Analytics

Although standard deviation is one of the most widely used methods of quantifying the spread in a sample, there are cases in which it fails to express the spread in the presence of an outlier.

You might be wondering how you can identify an outlier in a data. Outliers are data points whose values differ significantly from other data points in a dataset.

In most cases, it would be evident in the data visualisation or in the sample. A quick way to find an outlier is to calculate the standard deviation. If the result is much higher than expected, then there is a high chance that your data contains an outlier.

In such cases, the interquartile spread is a much better way to communicate the variation or the spread in the data. For example, it would be better to check the interquartile ranges of a data set that contains the weight of a certain species of animals.

The interquartile ranges would give you an idea about where the most data points are concentrated and what is the value that can be taken as a typical weight for that species. Quartile values are the values at the 25th, 50th, 75th and 100th percentiles in a sample. The standard Excel function can help you evaluate these values in a sample.

In the upcoming video, Thomas will show how to compute interquartile ranges.

In the video, you learnt how interquartile ranges can be calculated to get an idea about the spread of the data points

The formulas for calculating the interquartile ranges are as follows: 

1. Quartile 1: Value of the (n+1)/4th item when all the data points are arranged in ascending order
2. Quartile 2: Value of the (n+1)/ 2 th item when all the data points are arranged in ascending order
3. Quartile 3: Value of the 3(n+1) / 4th item when all the data points are arranged in ascending order

where n is the number of observations or data points. 

After calculating the interquartile ranges, the quartiles can be used to represent what is called as a box plot. It is a useful graphical representation tool that can help in having a quick look at how the data is distributed and also spot any outliers. 

Now, in the upcoming video, you will look at using inter-quartile ranges to form a box plot in Excel. 

In the video, you saw that you can use the QUARTILE function to calculate quartiles in Excel and use those quartiles to create a box plot. 

Suppose a sample data is contained in cells A1 to A20 of a worksheet. In such a case, the table below highlights the QUARTILE function in Excel.

Data can be easily represented using Box Plots in Excel. A box plot is useful for revealing the spread in data.