+ Reply to Thread
Results 1 to 5 of 5

Calculate Stdev for a group of cells based on multiple conditions

  1. #1
    Registered User
    Join Date
    08-13-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2013
    Posts
    3

    Calculate Stdev for a group of cells based on multiple conditions

    Hey,

    This is my first post so I hope I am doing everything alright. I am trying to find the standard deviation for a range of cells that meet 4 conditions. Here is what I am trying, and I have used CSE and simply enter:

    =STDEV.P(IF(AND($AO$10:$AO$1030<BB$7,$AO$10:$AO$1030>BA$7,$AW$10:$AW$1030<$BA8,$AW$10:$AW$1030>$BA7),$D$10:$D$1030))

    Basically, if it a value in AO is between two numbers and a value in AW is between two numbers, then I want the corresponding cell (same row) in column D to be included in that group. And I then want the stdev.p of that group of numbers.

    I also know that the conditions ARE being met because I did a countifs and an averageifs with the same conditions and it worked.

    Can somebody help me with this one??

    Thanks!

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

    Re: Calculate Stdev for a group of cells based on multiple conditions

    Quote Originally Posted by dsgeller View Post
    I have used CSE and simply enter:
    =STDEV.P(IF(AND($AO$10:$AO$1030<BB$7,$AO$10:$AO$1030>BA$7,
    $AW$10:$AW$1030<$BA8,$AW$10:$AW$1030>$BA7),$D$10:$D$1030))
    As you discovered, we cannot use the AND function in an array-entered formula with the intent that you have in mind.

    You could use multiplication to effect an AND operation, to wit (CSE):
    Please Login or Register  to view this content.
    But it is probably more efficient to use nested IF functions, to wit (CSE):
    Please Login or Register  to view this content.
    Caveat: If it is possible that not all conditions will be met in some circumstances, it would be prudent to wrap IFERROR around the STDEV.P expression. But if you require Excel 2003 compatibility and save to an "xls" file type, you will have to use ISERROR.
    Last edited by joeu2004; 08-13-2014 at 04:41 PM. Reason: cosmetic

  3. #3
    Registered User
    Join Date
    08-13-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2013
    Posts
    3

    Re: Calculate Stdev for a group of cells based on multiple conditions

    Thank you Joe! Sanity is restored! If you get a chance, I'd love to hear a basic description for why nested IFs are more efficient, but don't sweat over it.

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

    Re: Calculate Stdev for a group of cells based on multiple conditions

    Quote Originally Posted by joeu2004 View Post
    You could use multiplication to effect an AND operation, to wit (CSE):
    Please Login or Register  to view this content.
    But it is probably more efficient to use nested IF functions, to wit (CSE):
    Please Login or Register  to view this content.
    Quote Originally Posted by dsgeller View Post
    I'd love to hear a basic description for why nested IFs are more efficient
    First, I should have written: the second form is "generally more reliable and might be more efficient".

    Second, I suspect you would not notice any performance difference with such a relatively small range (1021 rows).

    But just to explain what I had in mind....

    In the first form, just considering the "AND" operation, Excel creates at least 2 temporary arrays [1] and evaluates k arrays of n elements and performs k*n multiplications, for k "AND" arguments, each with n rows. And any error among the "AND" arguments causes the entire formula to fail with an error.

    [1] I don't know anything about the internal implementation of Excel. I am making inferences based on the Evaluate Formula feature, which is not always correct.

    In the second form, Excel creates k temporary arrays and evaluates k arrays of n elements; so it might be less efficient in memory usage. But Excel processes the boolean logic left to right only as far as the first FALSE. So it might perform fewer operations. And any error to the right of the first FALSE has no effect on the result of the formula.

    For example, if AW1030=#NUM, but AO1030>BB7, my first formula returns #NUM, but my second formula evaluates STDEV.P, ignoring the #NUM error.

  5. #5
    Registered User
    Join Date
    08-13-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2013
    Posts
    3

    Re: Calculate Stdev for a group of cells based on multiple conditions

    Nice. Thank you again. I will definitely be posting again

+ 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. Populate Excel cells based on multiple IF conditions in VBA
    By muszynka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2013, 07:58 PM
  2. Combine text from multiple cells, Based on conditions
    By dark_horizon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-19-2012, 11:07 AM
  3. Replies: 7
    Last Post: 06-12-2009, 03:56 AM
  4. Calculate Values based on Multiple Conditions
    By creativefusion in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-24-2009, 09:27 PM
  5. Averaging and STDev based upon conditions
    By KrisM27 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2007, 06:45 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