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!

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.

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.

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