Calculating STDEV for non zeroes

[PJS]

Hi to whom might be of assistance

I am trying to calculate the Standard Deviation for a set of data while
excluding zeroes. Ley say, I have the following data set

1, 2, 3, 4, 5, 0
2, 1, 3, 0, 5, 4
0, 2, 4, 6, 8, 10

I can calculate the average of the first line of data by sum(A1:F1) /
countif(A1:F1)
but I am not sure how to calculate the STDEV while excluding the zeros where
ever it may be.

Is there a way to do so?

Thanks,

PJS

 

[Mike H]

Try this

=STDEV(IF(A1:F1<>0,A1:F1))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike