+ Reply to Thread
Results 1 to 2 of 2

standard deviation excluding outliers

  1. #1
    Forum Contributor
    Join Date
    11-22-2006
    MS-Off Ver
    2010
    Posts
    201

    standard deviation excluding outliers

    hi all,

    i have a range of numbers in C5:AL5
    Not all of the cells are filled in, and some that are filled in are blank.

    In AM5 I calculate the average of the data
    and in AN5 the standard deviation where the value in the cells in the range are >0

    Is it possible to calculate a standard deviation in another cell which excludes those values in the range which are 3 standard deviations away from the value in cell AN5.
    Therefore, the standard deviation with the outliers.

    If there is another way of doing this, i would be grateful for your help.
    Luce

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: standard deviation excluding outliers

    Try this array formula

    =STDEV(IF(ABS(C5:AL5-AN5)<AN5*3,IF(C5:AL5>0,C5:AL5)))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1