S

#### Shane Lindsay

mean of a range but not include or change values that are 2 or 3

standard deviations above or below the mean. Excel has a trimmedmean

function but outlier removal based on the above criteria is considered

better for normally distributed data.

I found these formula posted before on this forum. They don't include

values 2 SD above or below the mean. And they work!

=AVERAGE(IF((ABS(rng-AVERAGE(rng)))>(2*STDEV(rng)), "", rng))

=TRIMMEAN(Rng,COUNTIF(Rng,">"&(AVERAGE(Rng)+2*STDEVP(Rng)))/COUNT(Rng))

I also need to know how many data points were "removed", and then

report that as a percentage, and couldn't figure out to that (bearing

in mind they could be missing data points).

An alternative method of outlier analysis is to:

a. replace outliers with the 2 standard deviations above or below value

b. replace outliers with the mean

Any suggestions on how to do these things would be gratefully received.