Excluding outlier value based on average

[abhi254]

Hello everyone,

I am facing problem in excluding outlier value from excel. I have set of data say about 200. From these set of data I need to take average. The problem is the values which are outside 50 to 150 % of average values needs to be excluded subsequently in consideration of average. (like circular reference). I need to exclude the most deviant value first then recalculate the average, again exclude the value outside limit and so on till all accepted values are within 50 to 150%. Can anyone help on this query? Excel formula or macro shall also be acceptable.

[MrShorty]

How automated does this need to be? I do this sort of thing occasionally, and I will setup a column with a hand entered toggle value (usually 1 or 0) to indicate which values should be included in the average. Then a simple =AVERAGEIF() (http://office.microsoft.com/en-us/ex...in=HP010342920) function can be used to get the average. Another column will calculate the deviation from the average value for each data point. Then all I need to do is manually toggle the values in the toggle value column for those points that I consider outliers. One would usually hope that there are not very many outliers, so this process usually doesn't take too long.

If you decide this needs to be automated, I would probably use the same basic layout. Apply the correct combination of fixed and absolute references to the cells, then copy the columns across. Replace the hand entered values in the toggle columns with an appropriate =IF() function. Copy across until the result of the AVERAGEIF() function stops changing.

If you set it up right and test it carefully, you might be able to get this to work by enabling iteration and building the circular reference. I would be worried that it may not converge on a single value, so I would definitely suggest you test it carefully.