+ Reply to Thread
Results 1 to 3 of 3

Standard Deviation formula removing outliers

  1. #1
    Registered User
    Join Date
    01-16-2018
    Location
    Nebraska
    MS-Off Ver
    10
    Posts
    2

    Standard Deviation formula removing outliers

    Hello!
    I am new to this forum, this is my first post, so please forgive me if I make a mistake or two.

    I am trying to do some calculations for Standard Deviation of data in a column. I would like the results to be in a cell in that column, on the bottom. This isn't a real issue, except that I have some outliers that I am scientifically required to remove from the data. I cannot seem to find a formula that works for me. This is the base formula for standard deviation that I am working with:=IFERROR(STDEV.S(L8:L32),""). The if error statement is to eliminate the counting of blank cells in the table. From I have tried IF and IFS statements to no avail. I would like to do the standard deviation and have it automatically not count cells in the column that are less than 44 and greater than 55. After I am able to get the standard deviation, I then need to calculate estimate of uncertainty, which looks like this:=IFERROR(L36/SQRT(COUNT(L8:L32)),""). Again, IF ERROR is for blank cells.

    Any help is appreciated, thank you!

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

    Re: Standard Deviation formula removing outliers

    Hello Steve,

    One option is to use an array formula, I.e.

    =IFERROR(STDEV.S(IF(L8:L32>=44,IF(L8:L32<=55,L8:L32))),"")

    Confirm with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-16-2018
    Location
    Nebraska
    MS-Off Ver
    10
    Posts
    2

    Re: Standard Deviation formula removing outliers

    That looks good! I was missing the (=) and that was it. I will post another thread if I have the same issue with the Uncertainty portion. Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Standard deviation if formula
    By T15K in forum Excel General
    Replies: 3
    Last Post: 12-26-2015, 11:50 PM
  2. Standard Deviation Excluding Outliers
    By Brennen81 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2015, 08:28 PM
  3. Standard Deviation removing Max & Min
    By RogerThat88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-17-2014, 11:06 AM
  4. [SOLVED] Excel 2007 : standard deviation and if (FORMULA)
    By emfgirl in forum Excel General
    Replies: 6
    Last Post: 07-17-2012, 02:35 PM
  5. standard deviation excluding outliers
    By tinkerbelle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2010, 05:15 PM
  6. Excel 2007 : standard deviation and if (FORMULA)
    By alba123 in forum Excel General
    Replies: 3
    Last Post: 11-11-2010, 01:51 PM
  7. Standard Deviation Formula
    By techwriter80 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2010, 11:14 AM

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