Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Standard Deviation including blank cells

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Standard Deviation including blank cells

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Standard Deviation including blank cells

=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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Standard Deviation including blank cells

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 265
Default Standard Deviation including blank cells

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Standard Deviation including blank cells

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Standard Deviation including blank cells

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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Standard Deviation including blank cells

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Standard Deviation including blank cells

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
standard deviation [email protected] Charts and Charting in Excel 3 April 16th 08 01:04 AM
Standard deviation Svi Excel Discussion (Misc queries) 5 October 15th 07 10:13 AM
Standard deviation of specific cells in a column Schwartz Excel Discussion (Misc queries) 1 April 4th 07 12:06 PM
standard deviation ckatz Excel Worksheet Functions 1 October 25th 06 08:31 PM
Standard Deviation Stan Banner Excel Worksheet Functions 1 March 12th 06 01:54 AM


All times are GMT +1. The time now is 12:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"