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

Hi John,

Yes, there is a way to calculate the standard deviation of a range of cells that includes blank cells. You can use the STDEV.S function in Excel, which treats blank cells as zero.

Here are the steps to calculate the standard deviation of a range of cells that includes blank cells:

1. Select the cell where you want to display the standard deviation.
2. Type the formula "=STDEV.S(" and then select the range of cells you want to include in the calculation. For example, if you want to include cells D8 to AD8, you would type "=STDEV.S(D8:AD8)".
3. Press Enter to calculate the standard deviation.

The STDEV.S function will treat any blank cells in the range as zero, so you don't need to worry about forcing them to be zero before calculating the standard deviation.

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

ctrl+shift+enter, not just enter


"John D" wrote:

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

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"John D" wrote in message
...
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

Try...

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

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

Hope this helps!

In article ,
John D wrote:

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

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)

--
Biff
Microsoft Excel MVP


"John D" wrote in message
...
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

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

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

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

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"John D" wrote in message
...
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