calculate standard deviation given criteria and empty cells

[newfie675]

Hi, I have a spreadsheet that multiple users will be pasting data into and depending on the analytical company it will vary from blank (no analysis) or <value (below detection limits) or >value (above detection limits). I am trying to calculate the standard deviation of an array with specific criteria and the cell has a numerical value.

For example I have a spreadsheet that contains: column A with Company X and Company Y, Column B with numerical data, blank cells and text value (e.g. <0.03 or >10000).
I am using Shift+Ctrl+Enter to get the {}
If I calculate the standard deviation of Company X by sorting Column A and Column B =STDEV(B2:B9) I get a value of 10 which is the correct value
I have tried {=STDEV(IF(AND(A2:A21="Company X",B2:B21<>0),B2:B21))} I get a Standard deviation of #DIV/0!
I have tried {=STDEV(A2:A21="Company X"),B2:B21)} I get a Standard deviation of 251 which calculates blank and text cells as 0
If I use {=STDEV(IF(A2:A21="Company Y",B2:B21))} that does not have blanks or text the standard deviation is calculated correctly

Any help would be greatly appreciated.
Thanks

 

[joeu2004]

Array-enter the following formula:

=STDEV(IF(A2:A21="Company X",IF(B2:B21<>0, B2:B21)))

We cannot use functions like AND and OR in array-entered formulas because they process the entire array/range parameter, instead of evaluating row-by-row, as you intend. In other words, AND(A2:A21="X",...) is equivalent to AND(A2="X",A3="X",...,A21="X",...).

FYI, I get 11.1066260789289 for Company X, which rounds to 11, not 10. But that might be because the values in column B are not actually the integers that you display. Right?

I get 15.7247530073721 for Company Y, which does round to 16.

I hope you know that the text "<15" is ignored by STDEV.

 

[newfie675]

Thank you so much that does work.
For Company X I had 1 blank cell in the formula by mistake. That is why I was getting 10 and not 11. Removing the blank cell I get 11 as well.