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.
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.
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.
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.
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.
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
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 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.
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.
+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
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.