+ Reply to Thread
Results 1 to 3 of 3

Standard Deviation of Dynamic Range

  1. #1
    Registered User
    Join Date
    05-26-2009
    Location
    new york
    MS-Off Ver
    Excel 2007
    Posts
    76

    Standard Deviation of Dynamic Range

    Hello,

    I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

    =STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0))

    *This is an array formula calculated by pressing CTRL+SHIFT+ ENTER

    The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation. Could someone please help me with this?

    Kind Regards,

    Maani

    http://www.4shared.com/file/12409801...c74/Book1.html
    Last edited by maani; 08-10-2009 at 07:45 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Standard Deviation of Dynamic Range

    I think perhaps you want:

    =STDEV(IF(ISNUMBER(A3:A20000),A3:A20000))
    confirmed with CTRL + SHIFT + ENTER

    (at the moment you're conducting a standard deviation of the Boolean outputs 1/0)

  3. #3
    Registered User
    Join Date
    05-26-2009
    Location
    new york
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Standard Deviation of Dynamic Range

    That works perfectly! Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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