# Thread: Statistical analysis Excel VS MiniTab (97 SP2)

1. ## Statistical analysis Excel VS MiniTab (97 SP2)

I would like to perform a statistical check on the following data.
193, 159, 145, 240, 116, 2062, 294, 200, 222, 209

However, the number 2062 is stray and needs to be either discarded or replaced with an average. I want to discard it and am looking for a simple way to do this. The only way I can find is to create a second data set minus the extreme values. Is there a better way.

Thanks.

2. ## Re: Statistical analysis Excel VS MiniTab (97 SP2)

Hi Cecil,

What "statistical check" would you have in mind?
What do you need to discern? whether the data has outliers?

3. ## Re: Statistical analysis Excel VS MiniTab (97 SP2)

I know the data has outliers. We are doing statistical analysis on production cycletimes and the clocks do not stop for break or lunch. I want the Average (mean) of all the data except the outliers. Same with mode, Standard Deviation. I can use an array formula like (CycleTime is named range) {=Average((CycleTime<1500)*cycletime)}, but now the resulting array contains zerro's. Therefore I am skewed in the opposite direction.

Now that I have written the above, I can see what I missed. The statistical functions like StDev ignore boolean values. Therefore {=STDEV((IF(CycleTime>1500,FALSE,CycleTime)))} works.

4. ## Re: Statistical analysis Excel VS MiniTab (97 SP2)

If the outliers are always off by a large amount, your array formula method is fine. Of course you could refine it by setting the limit as a function of -say- +/- 3 sigma of the datarange in question.

BTW: strings are excluded from STDEV and AVERAGE as well, so you could replace the FALSE with "" (though there is not much point in doing so).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•