Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    If there is a better method, please advise. Thanks.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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