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
=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