Calculating Standard Deviation while ignoring Zeros
 

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

Calculating Standard Deviation while ignoring Zeros
 

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

Calculating Standard Deviation while ignoring Zeros
 

=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

Calculating Standard Deviation while ignoring Zeros
 

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

Calculating Standard Deviation while ignoring Zeros
 

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

Calculating Standard Deviation while ignoring Zeros
 

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