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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
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

Thanks for the help Jon, that worked great.



Jon Peltier wrote:
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
_______


wrote in message
oups.com...
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

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

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

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

Thanks for your help everyone.


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

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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