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.
Mike

 

[Jon Peltier]

Try this array formula:

=STDEV(IF(range<>0,range))

Type the formula, put the address of the range where it says range, and hold
CTRL+SHIFT while pressing Enter. Excel will surround the formula with {curly
braces} if the formula is entered correctly.

- Jon

 

[Tom Ogilvy]

=stdev(if(A1:A100=0,false,A1:A100))
Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

 

[merjet]

This user-defined function should work.

Function StDevEx0(X As Range) As Double
Dim dArray() As Double

ReDim dArray(0)
For Each c In X
If c <> 0 Then
If dArray(0) = 0 Then
dArray(0) = c
Else
ReDim Preserve dArray(UBound(dArray) + 1)
dArray(UBound(dArray)) = c
End If
End If
Next c
StDevEx0 = Application.WorksheetFunction.StDev(dArray)
End Function


Hth,
Merjet

 

[kendrickmr]

Thanks for the help Jon, that worked great.

Try this array formula:

=STDEV(IF(range<>0,range))

Type the formula, put the address of the range where it says range, and hold
CTRL+SHIFT while pressing Enter. Excel will surround the formula with {curly
braces} if the formula is entered correctly.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[kendrickmr]

Thanks for your help everyone.

=stdev(if(A1:A100=0,false,A1:A100))
Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.