How to compute StDev of only nonzero entries in a range?
 

I know that one can compute the average of nonzero entries in column C by
using the formula C378=SUM(C387:C566)/(COUNTIF(C387:C566,"0")).
However, how can one compute the standard deviation of nonzero entries in
the range C387:c566?


Thank you for any help that you may be able to give.

Try the following
=AVERAGE(IF(C387:c566=0,"",C387:c566))

=STDEV(IF(C387:c566=0,"",C387:c566))

for both the formulae, press control + shift + enter as they are array
formulae

- Mangesh



"Sam" wrote in message
...
I know that one can compute the average of nonzero entries in column C by
using the formula C378=SUM(C387:C566)/(COUNTIF(C387:C566,"0")).
However, how can one compute the standard deviation of nonzero entries in
the range C387:c566?


Thank you for any help that you may be able to give.

How to compute StDev of only nonzero entries in a range?
 

I want to calculate a standard deviation of a subset of numbers in a column
if the value in ANOTHER column is 0. I tried
=STDEV(IF(B387:B566=0,"",C387:c566))
but it doesn't seem to work.
Any suggestions would be greatly appreciated. Thanks!


"Mangesh" wrote:

Try the following
=AVERAGE(IF(C387:c566=0,"",C387:c566))

=STDEV(IF(C387:c566=0,"",C387:c566))

for both the formulae, press control + shift + enter as they are array
formulae

- Mangesh



"Sam" wrote in message
...
I know that one can compute the average of nonzero entries in column C by
using the formula C378=SUM(C387:C566)/(COUNTIF(C387:C566,"0")).
However, how can one compute the standard deviation of nonzero entries in
the range C387:c566?


Thank you for any help that you may be able to give.

How to compute StDev of only nonzero entries in a range?
 

if the value in ANOTHER column is 0.
=STDEV(IF(B387:B566=0,"",C387:c566))

Those don't match.

Should it be greater than 0 or equals 0? There's nothing wrong with the
formula (if you array entered it) so you'll have to tell us *exactly* what
the problem is. Doesn't seem to work isn't descriptive enough!


--
Biff
Microsoft Excel MVP


"KO" wrote in message
...
I want to calculate a standard deviation of a subset of numbers in a column
if the value in ANOTHER column is 0. I tried
=STDEV(IF(B387:B566=0,"",C387:c566))
but it doesn't seem to work.
Any suggestions would be greatly appreciated. Thanks!


"Mangesh" wrote:

Try the following
=AVERAGE(IF(C387:c566=0,"",C387:c566))

=STDEV(IF(C387:c566=0,"",C387:c566))

for both the formulae, press control + shift + enter as they are array
formulae

- Mangesh



"Sam" wrote in message
...
I know that one can compute the average of nonzero entries in column C
by
using the formula C378=SUM(C387:C566)/(COUNTIF(C387:C566,"0")).
However, how can one compute the standard deviation of nonzero entries
in
the range C387:c566?


Thank you for any help that you may be able to give.