How to identify outliers?

Hi Wes,

You can add a Trendline and the equation to your data. Then using the equation simply find the square of the distance from the point to the line. Conditional format the biggest distances and see if that is what you need.

See http://www.excel-easy.com/examples/trendline.html for adding a trendline.

When you toss out data it is like cheating. You are really simply throwing away data that doesn't support what you want it to support. Why collect data if you aren't going use it?

I believe you'd use a statistical term dealing with standard deviations away from the Trend Line. I'd say the data point was outside 3 standard deviations of the trend.

Originally Posted by Wes-at-UoB

If the data is statistically determined as outlier, I'm happy to exclude it.

Technically, you should only exlcude outliers if they represent an error in the data. But in practice, it is not uncommon to exclude outliers without understanding their nature.

Originally Posted by Wes-at-UoB

I am not sure if I can use standard deviation in this case?

You are wise to question that. The std dev rule should be applied only to data that is normally distributed. Unfortunately, many people overlook that fact.

More generally, use the IQR (interquartile range) rule to identify outliers. (The operative word is "identify". Again, just because data are outliers, that does not mean they should be excluded.) For example:

Q1: =QUARTILE(B2:B38,1)
Q3: =QUARTILE(B2:B38,3)
IQR1: =Q3-Q1
OUT1: =Q1-IQR1*1.5
OUT3: =Q3+IQR1*1.5

Then in C2:C38:
=IF(OR(B2<$OUT$1,$OUT$3<B2),"outlier","")

Note: There is no agreement on what IQR factor to use. 1.5 is often suggested because it is easy to remember, and it is "close" to 3sd if the data were normally distributed. In fact, 1.7 is better for that purpose. But some people prefer to use 4sd to identify outliers of a normal distribution; and to that end, IQR*2.5 is close to 4sd if the data were normally distributed.

joeu2004 may be a stronger statistician than I am, so I would defer to his judgement in the details. The only thing I would add to this discussion is a link to this discussion: http://www.real-statistics.com/multi...d-influencers/ One thing I like about his website is that he teaches the statistics principles and theories specifically for an Excel audience. If you need to perform these tests with some statistical rigor, I would suggest sites like this to help you understand the statistics behind the question and solution.