+ Reply to Thread
Results 1 to 5 of 5

Conditional StDev Produces Erroneous Results

  1. #1
    Registered User
    Join Date
    11-07-2017
    Location
    Binghamton, NY
    MS-Off Ver
    2016
    Posts
    15

    Conditional StDev Produces Erroneous Results

    I am using a 2-condition Conditional StDev formula as follows:

    {=STDEV(IF((Range1=Value1)*(Range2=Value2),Range3,))} [ctrl + shift + enter]

    I am finding that, while the formula will produce a result, upon testing, the result is consistently incorrect. I'm hoping that I am somehow using the method incorrectly, and look to this forum for feedback. The following describes two different methods of calculating the Standard Deviation of the values in column z, for which x = Black and y = Yellow. The table of x, y and z values is below.

    Calc Type StDev Result Formula
    StDev(z1,z2,…zn) 16.58 =STDEV(Value1, Value2)
    Conditional StDev 20.71 {=STDEV(IF((Range1=Value1)*(Range2=Value2),Range3,))}


    x y z
    Black Yellow 37
    Black Green 13
    Black Yellow 53
    Black Green 68
    Blue Yellow 69
    Blue Green 81
    Blue Yellow 89
    Blue Green 50
    Black Yellow 60
    Black Green 24
    Black Yellow 23
    Black Green 93
    Blue Yellow 52
    Blue Green 46
    Blue Yellow 94
    Blue Green 23
    Last edited by mjcarman01; 11-15-2017 at 04:42 PM. Reason: Could not attach example file. Included contents.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Conditional StDev Produces Erroneous Results

    It's a little hard to speculate without an example.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Conditional StDev Produces Erroneous Results

    Quote Originally Posted by mjcarman01 View Post
    I am finding that, while the formula will produce a result, upon testing, the result is consistently incorrect.
    [....]
    Calc Type StDev Result Formula
    StDev(z1,z2,...zn) 16.58 =STDEV(Value1, Value2)
    Conditional StDev 20.71 { =STDEV(IF((Range1=Value1)*(Range2=Value2),Range3,)) }
    Hard to say what your mistake is, since you do not show us the actual formulas. (Klunk!)

    The following works just fine for me:

    Please Login or Register  to view this content.
    PS.... I would use STDEV.P. STDEV.P is the actual std dev of the data. STDEV is an approximation of the std dev of an unspecified larger population of data, of which the data above is a sample (ideally a random sampling).

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

    Re: Conditional StDev Produces Erroneous Results

    Quote Originally Posted by mjcarman01 View Post
    =STDEV(IF((Range1=Value1)*(Range2=Value2),Range3,))
    It's that comma at the end that's the problem, that's the same as doing this:

    =STDEV(IF((Range1=Value1)*(Range2=Value2),Range3,0))

    so on rows where the conditions aren't satisfied the formula returns zero and that skews the result -take out the comma and use this version:

    =STDEV(IF((Range1=Value1)*(Range2=Value2),Range3))

    where the conditions aren't satisfied you get FALSE which STDEV function will ignore
    Audere est facere

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Conditional StDev Produces Erroneous Results

    Quote Originally Posted by daddylonglegs View Post
    It's that comma at the end that's the problem
    Good eye! .

+ 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. Array formula returns erroneous results
    By RJK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2017, 01:25 PM
  2. [SOLVED] Offset - same formula produces different results
    By BRISBANEBOB in forum Excel General
    Replies: 4
    Last Post: 09-23-2016, 07:40 PM
  3. STDEV: Different results. Bug-like in Excel 2010
    By Yemitubosun in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-02-2015, 06:00 AM
  4. [SOLVED] Formula produces unexpected results
    By furface00 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-04-2014, 02:17 PM
  5. Same vba routine produces different results
    By john1674 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2013, 05:44 AM
  6. visual basic conditional formatting produces strange results
    By Carl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2006, 10:10 AM
  7. [SOLVED] StDev Results
    By Michael in forum Excel General
    Replies: 1
    Last Post: 08-22-2005, 05:05 PM

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