Identifying and Highlighting Outliers in Excel
HTML-код
- Опубликовано: 17 июл 2015
- This video demonstrates how to create identify and highlight outliers using Excel by calculating and evaluating Z scores. The STANDARDIZE function and conditional formatting are used to highlight outliers in a variable.
Sorry, where did the value 2.68 come from?
The technology of this program saves a lot of time with how quickly and easy you obtain the data needed. I have always looked at outliers through graphs so it is good to know about the convenience with Excel.
This was pretty straight forward, once you understand how to determine the 2.68 it is pretty easy to see where the outliers will be.
Using conditional formatting is a great tool to spot the outliers quickly. It was interesting that you highlighted the test region using the z score data. Another great video on how to get excel to work for you.
I appreciate the organization and formatting that you use, it really helps keep things straight.
This was another really helpful video as a way to highlight outliers using Excel. I have seen others comment about SPSS and the differences. I used SPSS in my statistics class in my undergraduate, and there was parts that seem a little easier but your directions have clarified quite a bit.
The step by step instructions were really helpful and easy to follow along with. I have not used the standardize function in the past, but it seems really easy to use.
Very helpful video, doing this in excel gives more insight in the formula's and makes one smarter.
This calculation seems to fit with having correct z scores and finding when an absolute value is equal to the outlier. Being able to highlight the cells this quickly is a good tool. I like most will have to reference this again in future practice.
Though I remember many of these concepts, at least a little, from statistics, I had no idea everything Excel could do. I don't know if I will ever get the opportunity to work with SPSS so I am happy to know that I can use Excel for so much.
I found the information helpful, and am learning to like Excel more and more after watching these videos, however, I agree with some others below in that this seems more difficult than SPSS.
Thanks for your tutorial
Great reference for those who don't own SPSS and need to do some statistics and find the outliers in their data using Excel.
I appreciate your helpful practice!
Thank you!
Thank you for your nice video. Could you please tell me is there any way to find inlier error as well?
What is the rationale behind using the 2.5 or 2.68 rule? I'm not a statistician, but do control charting quite a lot, and the standard in statistical process control is +-3 sd. for outliers.
Great video, but what's the point of the min-max-range?
I love excel. It is such an intuitive program. But after watching SPSS I think it has more functionality overall. I thought that z scores were more sensitive than alpha's but the range in this example is actually greater than .05.
This is helpful for me especially when you have large data sets. I also got confused on how 2.68 was determined, but that was explained in the comments so that makes sense now.
I was also initially confused as to how he determined that number. As soon as I understood that it seemed so much easier to identify the outliers. I agree with you, it will be of great use for determining outliers in larger data sets
thank you so much!!
Very interesting but yes, I agree with other posters, it does seem easier in SPSS.
This will be another reference video, I am hopeful that I will be able to identify outliers without this method but I could see it being helpful for massive amounts of data.
I agree Greg, Seems simpler in SPSS,
using conditional formatting looks helpful to hi lite the outliers so they are not missed in the sea of numbers on an excel spreadsheet
How to choose between 2.5 and 2.68?
Can you please explain why this is different from finding the outliers using the 1.5 * IQR rule?
+Adrian Ward This is the same as the interquartile range method. The IQR of the standard normal distribution is -.67 to .67 (1.34). Multiplying 1.5 by 1.34 yields 2.01, and 2.01 + .67 = 2.68.
+Todd Grande I've used the 1.5 IQR for an assessment and then did your method. Using your method only obtained 2 outliers where using the 1.5 IQR found 4 outliers. When speaking to the statistics of weather, I was told that there was 4 outliers. So your method is missing something
can anybody please tell me how did he get 2.68?
The IQR of the standard normal distribution is -.67 to .67 (1.34). Multiplying 1.5 by 1.34 yields 2.01, and 2.01 + .67 = 2.68.
Todd Grande hi doctor..can you please assist about 0.67 ? How did you reach it ?
Sam Kab One std deviation of the normal distribution
@@DrGrande Thanks for this video, but I am Sorry Dr. Still not understand how did you get 2.68?
@@DrGrande Thank you for the explanation. But why we have to multiplying with 1.5? What is 1.5 ?
How did he determine he should use 2.68 vs 2.5? He doesn't say.
The 2.68 value comes from the "1.5 times the IQR rule." Under this definition of an outlier, an observation is an outlier if it is more than 1.5 times the interquartile range above quartile 3 (Q3) or below quartile 1 (Q1). Q1 is .67 standard deviations from the mean. Therefore, the IQR is 1.34 (2 times .67). Multiplying the IQR by 1.5 gives us 2.01. Adding .67 to 2.01, gives us 2.68.