Calculating Standard Deviation while ignoring Zeros

  • Thread starter Thread starter kendrickmr
  • Start date Start date
K

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
 
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
 
=stdev(if(A1:A100=0,false,A1:A100))
Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
 
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
 
Thanks for the help Jon, that worked great.



Jon said:
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
_______
 
Thanks for your help everyone.


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