Standard deviation based on 2 criteria
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Standard deviation based on 2 criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Standard deviation based on 2 criteria

    Hi there,

    I read a few other posts but had much difficulty modifying the formulas to match my needs.

    I have two columns, one with different times (i.e. 1:07, 2:25, etc.) and another with a different values that range from 1-200. I already used the averageifs function to find the average for each of the time ranges (0:00-0:59, 1:00-1:59, etc) but need to find a formula to find the corresponding standard deviations.

    Thank you in advance!

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    [deleted]
    Last edited by joeu2004; May 13th, 2014 at 01:05 PM.

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    The standard deviation of what, based on what criteria?

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    Quote Originally Posted by dolls View Post
    I have two columns, one with different times (i.e. 1:07, 2:25, etc.) and another with a different values that range from 1-200. I already used the averageifs function to find the average for each of the time ranges (0:00-0:59, 1:00-1:59, etc) but need to find a formula to find the corresponding standard deviations.
    It would help to see your AVERAGEIFS formula in order to fill in missing details. For example, your subject line says there are two criteria, but you mention only one (time range). Perhaps the following paradigm will suffice.

    Array-enter the following formula (press ctrl+shift+Enter instead of Enter):

    =STDEV(IF(A1:A1000>=TIME(1,0,0),IF(A1:A1000 <=TIME(1,59,0),
    IF(B1:B1000>=10,IF(B1:B1000<=190,B1:B1000)))))

    That calculates the sample std dev of values in B1:B1000 for 1:00<=time<=1:59 and 10<=value<=190, where time is in A1:A1000.

    (Use STDEVP if you want the population std dev. See the Excel help pages for the difference, if you don't know.)

    Note: This forum's GUI is mangling my formula. I really want to write "less than TIME(2,0,0)" for the condition 1:00<=time<2:00. But the GUI will not allow me to use the "less than" symbol in that context.

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    the GUI will not allow me to use the "less than" symbol in that context
    Add a space after it so it isn't interpreted as the start of a tag.

  6. #6
    New Member
    Join Date
    Mar 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    Quote Originally Posted by joeu2004 View Post
    It would help to see your AVERAGEIFS formula in order to fill in missing details. For example, your subject line says there are two criteria, but you mention only one (time range). Perhaps the following paradigm will suffice.

    Array-enter the following formula (press ctrl+shift+Enter instead of Enter):

    =STDEV(IF(A1:A1000>=TIME(1,0,0),IF(A1:A1000 <=TIME(1,59,0),
    IF(B1:B1000>=10,IF(B1:B1000<=190,B1:B1000)))))

    That calculates the sample std dev of values in B1:B1000 for 1:00<=time<=1:59 and 10<=value<=190, where time is in A1:A1000.

    (Use STDEVP if you want the population std dev. See the Excel help pages for the difference, if you don't know.)

    Note: This forum's GUI is mangling my formula. I really want to write "less than TIME(2,0,0)" for the condition 1:00<=time<2:00. But the GUI will not allow me to use the "less than" symbol in that context.
    The only criterion is time, but I am trying to find the population standard deviation of the second column based on a time range (I though that the upper and lower bound of the time range represents 2 criteria).

    The averageif formula that I used is =AVERAGEIFS(B:B,A:A,">=0",A:A,"<0.041667") . I didn't know how to incorporate the time into it, so I used the numerical version for each hour (1:00=0.0416667, for instance).

  7. #7
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    =stdevp(if(a1:a1000 >= 0, if(a1:a1000 <= --"1:00", a1:a1000)))

    The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

  8. #8
    New Member
    Join Date
    Mar 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    Quote Originally Posted by shg View Post
    =stdevp(if(a1:a1000 >= 0, if(a1:a1000 <= --"1:00", a1:a1000)))

    The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.
    Hmm, I tried the above formula and it is giving me a standard deviation that is different from the one that I would expect. I filtered the time column (column A) for all values within 0:00-0:59, copied and pasted this, and then took the standard deviation of the second column and this standard deviation is different from the one that I got when using the above formula.

  9. #9
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    Quote Originally Posted by dolls View Post
    The only criterion is time, but I am trying to find the population standard deviation of the second column based on a time range (I though that the upper and lower bound of the time range represents 2 criteria).
    shg demonstrates how to modify the formula I presented before as a paradigm. However, I would be careful with the "<=" and "<" conditions. Previously, you said the time ranges were "0:00-0:59, 1:00-1:59, etc". So I would write either <=--"0:59" or <--"1:00".

    TIME(1,0,0) and --"1:00" are the same thing. The double-negative (--) converts text to numeric. I use it myself. But it confounds some people.

    Quote Originally Posted by dolls View Post
    The averageif formula that I used is =AVERAGEIFS(B:B,A:A,">=0",A:A,"<0.041667") . I didn't know how to incorporate the time into it, so I used the numerical version for each hour (1:00=0.0416667, for instance).
    Two comments, if I may.

    First, use limited range references like B1:B1000 instead of full-column references like B:B. The latter usually causes Excel to process 1+ million rows in Excel 2007 and later, and to create 1+ million intermediate arrays in some contexts (not necessarily this one).

    Second, use "<1:00" in the AVERAGEIFS conditional parameter instead of "<0.041667". 0.041667 is an approximation of 1:00. In fact, in this case, 0.041667 is about 1:00:00.029. Since that is greater than 1:00, "<0.041667" is effectively equivalent to "<=1:00", which was expressly not your intent. It might skew your results somewhat.

  10. #10
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Standard deviation based on 2 criteria

    Quote Originally Posted by dolls View Post
    Hmm, I tried the above formula and it is giving me a standard deviation that is different from the one that I would expect. I filtered the time column (column A) for all values within 0:00-0:59, copied and pasted this, and then took the standard deviation of the second column and this standard deviation is different from the one that I got when using the above formula.
    Our postings crossed on the "ether". See my previous response regarding "<=--1:00" [sic, shg] v. "<--"1:00" (your intent). Does that resolve the difference that you see?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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