How to Find Outliers with Excel

hi I'm gonna quickly show you how to calculate outliers in your data using Excel you can complete this in just a matter of minutes now it's gonna be some steps here the first step we're gonna calculate quartiles now if you don't know what quartiles are it is not a necessity to find outliers in your data however it will help if you know what they are so I'm gonna leave a description in the comment section below the second step we're gonna calculate the upper limit and the lower limit of our data so anything above the top limit is gonna be an outlier anything lower than the lowest limit is going to be an outlier so we're gonna evaluate the results after we calculate them so I've quickly prepared some data for us you can see the data here is our array of data we can almost visually see where the outliers are but let's imagine you have a huge data set you don't have time to visually go through each one of those values and determine if they're an outlier or not so we're gonna leverage the functions within Excel to tell us if we have an outlier or not so here are the first couple of steps we're gonna calculate the first quartile the third quartile and then we're gonna calculate the interquartile range this is gonna allow us to determine the upper limit and the lower limit of our data so first let's calculate the first quartile we're gonna use a quartile function in Excel so equals then you can start typing quartile you can see the function there click it now the first argument is the array or your data highlight your data comma and then you can see a little dialog box pop up it gives us some options if we can calculate the minimum value the first quartile second quartile the third quartile so the maximum value value we know we want the first quartile click one close your parentheses hit enter now we know the first quartile let's get the third quartile same steps highlight your data then the only difference here we want the 3rd quartile you can type 3 or just click here close your parentheses now we want to calculate interquartile range now we need an absolute value data which means we needed to be positive so and because it's a range we know it's gonna be the difference between these two and it says interquartile range so just take the highest value minus lowest value or your quartile your third quartile minus first quartile and then you have the inner quartile range so what does that allow us to do that allows us to calculate upper limit of our data and the lower limit of our data so how do we do that go to the upper section we know anything higher so we want anything higher than our upper bound limit so how do we calculate the limit so we take our highest value which is our 3rd quartile and we add that to 1.5 times our inner quartile range and then we're gonna get the upper bound limit of our data now we want to get the lower limit of our data lower boundary we're gonna take our lowest quartile and because we want the lower limit subtract instead of adding and we know we want 1.5 times or inner quartile range and this is gonna give us our lower limit so now we know anything higher than this value is an outlier anything lower than its value it's so that's very easy to see we can see how is that but again we want to do this efficiently across a large data set so let's use a logical function to tell us if these values fall above our upper bound or lower than our lower bound so let's use the or function or and the lot first logical function says is this data higher than our upper bound limit and then we want the second logical argument we want to know if our value is less than our lower bound now let's get the answer so we know this is not an outlier now of course you want to drag this formula down but before we do that we need to make sure that these two sells a lot because if we drag it down they're going to change so take your f f2 which is where the value is and click make it an absolute value how do you do that just press f4 and we know we don't want this one to move either so press f4 hit enter now you can drag that formula down and you can see where your outliers are here's an outlier because we see true here isn't that like because we see true so it one two three outliers on our data that's how you quickly calculate outliers in excel thank you

Channel: Absent Data

Views: 175,007

Rating: 4.9407845 out of 5

Keywords: Excel, outliers

Id: dt1MQmMaf4E

Length: 6min 6sec (366 seconds)

Published: Sun Dec 03 2017