in the last two videos I've been discussing outliers first I showed you
how to use one of the most common and robust measures of outliers to identify
and even remove those outliers from the excel data set and cooler still I even
showed you how to remove them from a given table summary for me that was a
two key method which uses quartiles and the interquartile range to identify
outliers then in the last video I covered three other methods of all our
infection or removal the trim mean standard z-score and modified z-score
methods I also rounded things off a little bit by going into some more depth
about outliers how to detect them when to remove them and why all our detection
can be very valuable so if you haven't watched it all - make sure to go back
and check them all now in this video I actually want to go into Excel and
figure out how we can identify those outliers using the three methods of
entry from the last video hey everyone and welcome back to the engineering tool
box channel where I give you the tools you need to solve real-world engineering
problems so in the first video we got up to this point where we identified
outliers using the do key method now let's look at the three other methods
that I discussed and compare the results so first was the trim mean method that's
why I have a column for that and in order to calculate this we'll have to
use the trim mean function and we'll also make this an array function so
we're going to use an if statement to say if this part number is equal to this
array of parts then we're going to look at this array of values and then the
false will just enter a blank value because it should always be true and
then the percent will use is 10% so point one and 10% is a pretty typical
value but you might also want to use 20% or something like that that might be a
bit extreme depending on your sample size but what control shift enter to
make that an array function you'll notice these little brackets so let's
take a look at the pivot table of this will refresh our data sort of things up
to date and then we'll drop in the trim mean value and
average and just for comparison let's look at the hours per part we'll take
the average of that so this will be the original mean and we can see that
they're pretty much the same except for this one value so that was the only case
where it took out some values in order to give us a trim mean the reason it
only affected this part number is because we use the value of 10% for our
trim percentage that means it takes off 5% of the count or five percent of the
samples from both ends of the distribution so as part number was the
only one that had more than 20 samples so 5% of 20 is one so that means it
takes off one value from each end if it's less than 20 it actually rounds
down so it won't actually trim any values alright next let's look at the
standard z-score method for detecting outliers so standard z-score or a lot of
column and for this we're actually going to add another column and this is going
to be the standard deviation and the standard deviation is used to calculate
AZ value so we'll need to calculate that first and we're again we're gonna use an
array method we're gonna say standard deviation and we'll want there's two
here this is for the population standard deviation this is for the sample
standard deviation we're gonna use the sample and then we'll do the standard
deviation if same thing we're gonna match our part number to the array the
value of true is going to be our our part values like the false control shift
enter this in the parentheses okay there we go
this is going to be the standard deviation for each part number we'll
also need to add in the mean so the original mean or average this is going
to be average and if this part number equals the rave
then use hours per partner rate Elia falses playing close off the average
pick control-shift-n dirt so now that we have a standard deviation
of mean we can calculate the standard z-score and the z-score is calculated
with this equation basically we're finding out how many standard deviations
are there between a given sample and the media or mean of that sample so we could
just enter in or set up an equation manually but Excel also has a built-in
function for calculating this so we can say equals standardized find the x value
for that sample compare that to the mean of all the samples and then feed the
function the standard deviation for these samples and then when we hit enter
there we get our z-score values so if you remember from the last video then I
said that we can determine outliers by using the z-score and setting a limit
for the number of standard deviations that were allowing data to be considered
non outliers so I said we could use plus or minus two two-and-a-half or three
standard deviations or z-score values so let's just use three as our limit on the
z-score values so let's say a z-score outlier I'm glad I've gone for that then
we'll just set up an if statement say if the absolute value of this is greater
than three then true and if not then false
so now we can go back to our pivot table and see how the mean changes whether or
not B includes ZL layers so let's just kind of copy and paste these down to so
we can compare things so this is the original mean original mean and then
we'll say trim to mean and now if we add a slicer to our pivot table to look at
ZL our values we only want to look at values that are false so if we basically
exclude any Z outliers we can compare those values to our original means so
there's our original mean here is our mean with the Z outliers excluded then
let's actually add in the two key outliers this we can compare that as
well so for Tokyo iers add that in there and we want those to be false and that
to be true as well let's look at that looks like so now we can start to see
how each method returns different results so now let's look at the last
method which is going to be the modified z-score this one is tricky but it's
pretty robust so this is one of my favorites it works pretty well for small
sample sizes and ends up returning similar results to the Tukey method as
well because it uses median values to determine all tires so we're going to
need to add two columns here we're gonna say the MA D and modified z-score so the
MA D is going to be the median absolute deviation of each data point from the
meaning so that's kind of hard to explain so I'm actually gonna add
another column which will hopefully make it a little bit easier to understand so
this is going to be the median or actually the absolute
so now this value is going to be the difference from our data point and the
sample median so add that actually sorry that's the absolute value of that so
we're not concerned with direction we're just concerned with how far our data
point is from the sample median and the M ad then is going to be another array
function we're going to take the median of these values will go quartile just
because I like to use that formula and then this would be another array
function so we'll say if this part number matches the part array again then
we're going to look to our absolute deviation values nothing goes off the if
statement and then tell the quartile function which quartile we're looking at
and that's the second quartile for the median then control shift enter for that
and this is our median absolute deviation so this tells us how far each
data point is from the meeting so in order to calculate the modified z-score
now if we look back at our formula from our last video here the modified z-score
equals this constant times the difference between the data sample and
the sample median divided by the ma D let's build this formula into our table
here they say point six four six seven four five times the difference of our
data sample and our median quartile 2 and then all that divided by M ad and I
know if we go and through there these are going to be the values for a
modified z-score the last thing we need to do is add the column to say if it is
a mod z-score outlier so I'll add a column here mod Z outlier right quick if
statement to say if the absolute value of this
it is greater than 3.5 is typically the value used as a threshold for outliers
when using the modified Zeitz for method so if it is greater than 3.5 then we're
gonna return true to say yes it is an all liar and if not false it is not
enough like and then now we have identified our mod Z outliers so we can
go to our pivot table quick and take this filter off refresh our data here we
want to add a slicer here so we can filter out the false values or the true
values to filter out the outliers so I'll just select just the false here is
the mean after modified Z outliers have been removed so there are results so I'm
sure some of you might have been wondering why we were using array
functions they can be a little bit tricky but the reason we need to use
them is because we're trying to summarize our data by part number to get
an average excluding these certain all liars
that was kind of the overall goal that's just to kind of compare the different
methods and see what they do for identifying outliers how they change the
mean things like that then one other note just be cautious of using these
array functions in large data sets they're very nice that we can kind of
aggregate data by a different you know a category here like we did for the part
numbers but if this were a really long list the part numbers definitely a lot
bigger than this I'm talking in like the thousands it would really slow down the
calculations that Excel has to do in order to calculate our values so just be
cautious of that I've had spreadsheets before in the past that are in the
thousands of part numbers and it really gets bogged down so I'll just be
cautious all right I hope you all enjoyed that one to get more videos as
they are released make sure to hit that subscribe button if you like this video
make sure you like and share and if you didn't like it well then leave me a
comment and let me know how I can improve last but not least this channel
is for you so if you have any suggestions for topics you want me to
cover make sure to let me know thanks for watching we'll see you next time