Calculating Standard Deviation while ignoring Zeros

[kendrickmr]

I am working with a large data set and I am interested in calculating
standard deviation of over 100 samples, but I want to ignore any zero
values and only utilize the non-zero values in the calculation. Does
anyone know an easy way to do this. Thanks for the help.

 

[Harlan Grove]

(e-mail address removed) wrote...

I am working with a large data set and I am interested in calculating
standard deviation of over 100 samples, but I want to ignore any zero
values and only utilize the non-zero values in the calculation. Does
anyone know an easy way to do this. Thanks for the help.

If all the remaining values were positive after removing the zero
values, you could try the array formula

=STDEV(IF(rng>0,rng))

On the other hand, if you have positive and negative values, you
shouldn't exclude zero values. Note that Excel's STDEV function
excludes blank cells and cells containing text.