Understanding Outliers

3 min readMay 2, 2019

What is Outlier?
An outlier is a point which falls more than 1.5 times the interquartile(IQR) range above the third quartile(Q3) or below the first quartile(Q1).

First Quartile (Q1) is the middle number between the smallest number and the median of dataset.
Third Quartile (Q3) is the middle number between the median and highest value in dataset.
InterQuartile (IQR) is the difference between Q3 and Q1. (IQR = Q3-Q1)

How to detect Outlier?
A point that falls outside the dataset inner fences is classified as a minor outlier, while one that falls outside the outer fences is classified as a major outlier. To find the inner fences for your data set, first multiply the interquartile range by 1.5; then add the result to Q3 and subtract it from Q1.

Like for Upper Bound : Q3 +(1.5 * IQR)
and for Lower Bound : Q1 - (1.5 * IQR)
If the values in dataset goes above Upper Bound or falls below Lower Bound then that values is called to be an outlier.

Calculation of Outlier in Excel
Sample data:
33, 54, 33, 120, 64, 47, 92, 200, 231, 12, -100, 90, 77, 11

Now we will calculate Q1, Q3 and IQR as shown below

Formula’s of Q1, Q3 and IQR is been mentioned above

For Q1 in Excel:
=QUARTILE(A2:A15,1) => here the number 1 indicates First Quartile.

For Q3 in Excel:
=QUARTILE(A2:A15,3) => here the number 3 indicates Third Quartile.

For IQR in Excel:
=F2-E2

Next is to calculate Upper Bound and Lower Bound as shown below
The Upper Bound and Lower Bound value is the range.

Formula for Upper Bound in Excel:
=F2+(1.5*G2)

Get Arjun Sharma’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Formula for Lower Bound in Excel:
=E2-(1.5*G2)

Now let’s see which value is within the range of -54.75 to 179.25 and also we will get to know which value is an outlier.
For that I have used “OR” condition as =OR(A2>$H$2,A2<$I$2)

As we can see the values which is not in range are termed as “True” in Outlier column. We have 3 values as Outliers (200, 231 and -100)

Now as we have found the Outlier values, we can omit it or skip it so that we can get Mean value = Median value and also proper value for Standard Deviation.

Here we can see the Mean value and Median value is almost the same so we are on a right track and also we can see the changes in values of Q1, Q3, IQR, Upper Bound and Lower Bound after omitting Outlier values.

Now we will calculate Normal Distribution for “Data” column values

Now we will select all the values of Data and Normal Distribution and put them into a chart (go to insert-> select scatter->drag and drop Scatter with Smooth Lines and Markers) in Excel as shown below