Standard Deviation including blank cells

[John D]

I have a range of cells for which I want to calculate standard deviation -
*including* blank cells. Example, if (D8:AD:8) has numbers in 20 columns and
blanks in 6 columns, I want the formula to interpret those 6 blanks as "0".

The data is being imported from a large database and the data set being
imported will vary. I suppose I could somehow force all blank cells to be "0"
when imported, but I'd prefer not.

Any other way? Thanks - John D

 

[Teethless mama]

=STDEV(IF(D8:AD8>=0,D8:AD8))

ctrl+shift+enter, not just enter

 

[Bernard Liengme]

This works to have blanks count as zeros when entered as an array formula
with CTRL+SHIFT+ENTER
=AVERAGE(IF(ISBLANK(A1:E1),0,A1:E1))
Adjust to taste
Best wishes

 

[Domenic]

Try...

=STDEV(IF(D8:AD8<>"",D8:AD8,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[T. Valko]

Assuming the blank cells are *empty*:

Array entered** :

=STDEV(D8:AD8+0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[John D]

How fun - in the "More than one way to deviate a standard" department ...

All 4 of your suggestions worked, although they were all slightly different
from each other.

Teethless mama: =STDEV(IF(D8:AD8>=0,D8:AD8))
Bernard: =AVERAGE(IF(ISBLANK(A1:E1),0,A1:E1)) Adjust to taste
Dominic: =STDEV(IF(D8:AD8<>"",D8:AD8,0))
T. Valko: =STDEV(D8:AD8+0)

Ain't that a hoot!

Clearly, I need to learn about array formulas - the main thing I was missing.

Thanks all - John D

 

[John D]

However, under the rule that "simpler is better", I must say T. Valko's
solution of simply adding a "0" to each value in the array appears to be the
simplest solution.

=STDEV(D8:AD8+0)

John D

 

[T. Valko]

That's the one I'd use! <g>

Thanks for the feedback!