# Calculate Stdev for a group of cells based on multiple conditions

1. ## 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. ## Re: Calculate Stdev for a group of cells based on multiple conditions

Originally Posted by dsgeller
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.

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. ## Re: Calculate Stdev for a group of cells based on multiple conditions

Originally Posted by joeu2004
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.``
Originally Posted by dsgeller
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. ## Re: Calculate Stdev for a group of cells based on multiple conditions

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

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

#### 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