S
Shane Lindsay
I would like to use excel for outlier analysis removal - to calculate a
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.
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.