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
_______


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

=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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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
> _______
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >