Excel & Statistics 49: Outliers & Box Plot with Median and Mean

[oldcoder9000]

Excellent video. You are an excellent teacher.

[ExcelIsFun]

@oldcoder9000 , Thanks! I am glad that you liked the video!

[a3aan88]

Great video, it is very helpfull to me! I only have one question, can you explain why the z-score is 3? Is this always 3 or just in this case?

[ExcelIsFun]

@a3aan88 , z is just the number of standard devaitions and 3 is just one possibility; however, 3 standard devaitions should be a rare event and so it is sometimes used as the hurdle to determine outliers. Here is a video about z:

Excel & Statistics 45: Z-Score (# Standard Deviations From Mean)

or

Excel 2010 Statistics #31: z-Scores, Chebyshev's Theorem and Empirical Rule

[RakonKido]

why do people write 7 like that? >=O

[adriferr11235]

I've been using Excel for decades and I never knew about using F4 to refer to a specific cell. Extremely useful, thanks!

[basicphase]

this helped me so much when trying to remove outliers! while some were very minor, others made a big enough difference to be valid. and i did catch some statistical errors i made as well.

thank you!

[ExcelIsFun]

You are welcome!

[The4lbanian]

you sounds like ross from friends

[slingapow]

Thanks great videos!! but how could you put multiple box plots onto one graph?

[ExcelIsFun]

Created formulas in cells for as many as you want, created first chart, then go to Select Data Source dialog box and add extra series (extra box plot data).

[rotomatic80]

nvm lol

[rotomatic80]

my messes up when i try switching to scatter

[s916112]

MY HERO!!

[ExcelIsFun]

I am glad that it helped!

[alahalahelahamaha]

@ExcelIsFun Would you say that this is the best/most optimal method for outliers detection? I used Z-score and IQR, and the results are quite different - respectively 1.45% and 3.73% of the data denoted as outliers.

[thatismyopinionok]

great video! having stats for 4 months, failed in 2 tests and just with this video could understand how to do it! u saved my life! cheers mate!

[ExcelIsFun]

You are welcome!

[maverickgirl71]

I understand now!!!! :D Thanks for awesome teachers like you!! you have made my day

[ExcelIsFun]

You are welcome!

[msjoby89]

Thank you soooo much for this!! You are a great instructor!! Keep it up!! :) :)

[ExcelIsFun]

I am glad that you liked it!!

[wilfredoluoch]

Thank you so much for this video. I am a software developer in Nairobi, Kenya and that clip is so relevant for something a customer wants done. In face, I am now more interested in learning more statistics to add to my skill base. Ahsante Sana!

[ExcelIsFun]

You are welcome!!

[Alkurama]

How do you find the SD help!

[ExcelIsFun]

for sample use STDEV function, for population use STDEVP. See these videos:

Excel & Statistics 41: Sample Standard Deviation (Variability)

[snoopykid2991]

Thank you soooooo much for this video...you dont know how much of a big help this was...my text book was worthless when it came to doing this by hand but excel is a life savior. Thank you again for the help! ^.^

[ExcelIsFun]

You are welcome!

[tariqghuman]

Great Video! This was a life saver for me!!! :D

[ExcelIsFun]

You are welcome!

[kk717]

Outstanding job! thank you for doing such an amazing videos.

[ExcelIsFun]

You are welcome!

[777pinkfox]

I think you just saved my life with this. Seriously. Nothin' like working on a stats paper the night before its due and having no idea what your doing! Good times! Seriously. Thanks :D

[ExcelIsFun]

You are welcome!

[MOtheuniverse]

Thank you so much.

[ExcelIsFun]

You are welcome!

[rjcmr]

You've just made a box plot that doesn't tell or represent outliers... What kind of real use can it have? You still got to go back to your data and identify all outliers by eye! In a 50 value sample thats a little difficult...

[lisaisbizness]

Excellent video. It is very helpful when you can actually see how everything works!

[ExcelIsFun]

I am glad that it is helpful!

[ionleahu78]

Why Do we use Mean = 1 on the Y axle. To show the Mean in reference to Median?

[ExcelIsFun]

The 1 is to just trick the Chart Wizard and get it on the chart. Yes, some people like to see the mean in relation to median. It is not necessary to do it, though.

[13hazza]

When I switch between Row/ Column it adds all of the data together, so instead of rangeing from 0-200 it adds them all and ends up being around 700. How do you change it so it doesn't do that? Great video by the way.

[ExcelIsFun]

I do not understand what you are asking.

[13hazza]

It is ok. It was mistake on my part I didn't follow the part at 11:28 correctly.

[ExcelIsFun]

I am glad that it is working, now!!

[IcyAsh]

Oh my goodness. You're a lifesaver! I've been on the verge of tearing my hair out over the weekend looking for ways to determine an outlier and your video is the best one to demonstrate it so far!

By the way, you have way too many videos in your profile and I was wondering, do you have any video that teaches us how to transform data so it becomes normal (Gaussian)? I've been using log, square root, cube root, reciprocal, etc and it still doesn't look Gaussian.

Please help? Thanks.

[ExcelIsFun]

Dear IcyAsh,

No, I do not have any videos for that!

If you want a way to access videos by topic, watch this video title:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[mesq007]

How can we add the outliers to the boxplot, please?

[ExcelIsFun]

Dear mesq007,

You add them the same way that you add the mean (as in this video).

--excelisfun

[mesq007]

You're right, in fact I have tried it once.

The difficult task for me is to identife the outliers in the sample range, put it in a convenient table and finally add it to the plot.

Thank you very much!

[mazerollef]

Very nice. Why didn't you use the quartile function in Excel to get the quartiles automatically ?

[ExcelIsFun]

Dear mazerollef,

Earlier in the series (Videos 33 & 37) I showed how to do this. There are a few different statistical methods to calculate Quartiles/Percentiles. Excel does one particular method, so I wanted to show at least one of the other methods. However, when the data sets are large, all methods tend to yield similar results.

--excelisfun