Negative Standard Deviation?

  • Thread starter Thread starter PMK
  • Start date Start date
P

PMK

I am working with a list of 15 - 20 whole numbers and calculating the mean,
one std dev above and one std dev below. My data consists of many 0 values
as well as high numbers over 100. My std dev -1 calculation is coming out
negative. I would expect a large variation but not a negative std dev.
Since I understand std dev is the square root of a square, I have to assume
operator error. Can someone clarify what may be my error, not seeing my
data, or confirming that std dev can be negative? Thanks for your help.
 
Fascinating!

If something like:

=STDEV(A1:A26)

is coming out negative, please post the input data.
 
PMK said:
I am working with a list of 15 - 20 whole numbers and calculating the mean,
one std dev above and one std dev below. My data consists of many 0 values
as well as high numbers over 100. My std dev -1 calculation is coming out
negative. I would expect a large variation but not a negative std dev.
Since I understand std dev is the square root of a square, I have to assume
operator error. Can someone clarify what may be my error, not seeing my
data, or confirming that std dev can be negative? Thanks for your help.
Click to expand...


Obviously, the standard deviation is larger than the mean due to the large
number of zero values. Take the most extreme case of 14 zero values and one
value of 100. The mean is 6.667 (100/15) and the standard deviation is 25.82,
giving you -19.15 for mean minus one standard deviation.

If you are looking for a different result, maybe you want to exclude zero
values. For standard deviation and mean, respectively, use something like these
array formulas (commit with CTRL+SHIFT+ENTER):

=STDEV(IF(A1:A15<>0,A1:A15))

=AVERAGE(IF(A1:A15<>0,A1:A15))
 
Gary''s Student said:
Fascinating!

If something like:

=STDEV(A1:A26)

is coming out negative, please post the input data.
Click to expand...


That would be fascinating, but I don't think it's possible. I believe he meant
that "mean minus one standard deviation" was negative.
 
Back
Top