I have a spreadsheet with 52 data points on which I'm trying to identify a +1
standard deviation and a -1 standard deviation. When I use the Excel 2003
std dev function to calculate the info, I get a negative standard deviation
on the -1 calculation. Not being a stats wizard, I think it's mathematically
impossible for a standard deviation to be negative. I am assuming I have a
error in my data or formula. My data has a wide variation from a few around
1500 to many 0 and some data in between. Any thoughts would be greatly
appreciated.

You are correct that a negative standard deviation is mathematically impossible. It is likely that there is an error in your data or formula. Here are a few things you can try to troubleshoot the issue:

1. Double-check your data: Make sure that all of your data points are accurate and entered correctly. Check for any typos or missing values.
2. Check your formula: Make sure that you are using the correct formula to calculate the standard deviation. In Excel, the formula is
   Formula:

   =STDEV(range) 

   . Double-check that you have entered the correct range of cells.
3. Check for outliers: Outliers can greatly affect the standard deviation calculation. Look for any data points that are significantly higher or lower than the rest of the data. You may want to remove these outliers and recalculate the standard deviation.
4. Try a different method: If you are still getting a negative standard deviation, try using a different method to calculate it. You can use the AVERAGE and STDEV.S functions to calculate the standard deviation. The formula is
   Formula:

   =STDEV.S(range)/AVERAGE(range) 

   .

By following these steps, you should be able to identify and correct any errors in your data or formula and calculate a valid standard deviation.

StdDev is a measurement of the variablility of the data so you are correct in
assuming that there is no such thing as a negative StdDev. At 1 stddev about
2/3 of your data points will lie within 1 std dev of the average. 2 std dev
is around 95% of the data points. So the avearge temperature in January is 5
degrees +/- 3 degrees 67% of the time means that the temp is going to be
between 2 and 8 degrees most days. I would never calculate the -std dev as it
will be the same as the positive. Not sure if that makes sense...

Post your data pints and your formulas if you are still having trouble...
--
HTH...

Jim Thomlinson


"PMK" wrote:

I have a spreadsheet with 52 data points on which I'm trying to identify a +1
standard deviation and a -1 standard deviation. When I use the Excel 2003
std dev function to calculate the info, I get a negative standard deviation
on the -1 calculation. Not being a stats wizard, I think it's mathematically
impossible for a standard deviation to be negative. I am assuming I have a
error in my data or formula. My data has a wide variation from a few around
1500 to many 0 and some data in between. Any thoughts would be greatly
appreciated.

stdev is always positive. However:

mean+stdev
and
mean-stdev

may be negative if the mean is small or negative.
--
Gary''s Student - gsnu201001


"PMK" wrote:

I have a spreadsheet with 52 data points on which I'm trying to identify a +1
standard deviation and a -1 standard deviation. When I use the Excel 2003
std dev function to calculate the info, I get a negative standard deviation
on the -1 calculation. Not being a stats wizard, I think it's mathematically
impossible for a standard deviation to be negative. I am assuming I have a
error in my data or formula. My data has a wide variation from a few around
1500 to many 0 and some data in between. Any thoughts would be greatly
appreciated.

"Jim Thomlinson" wrote:
At 1 stddev about 2/3 of your data points will lie within 1 std dev of
the average.

That is the expected result for a normal distribution. It may or may not be
true for other distributions. As you say, the standard deviation is a
measure of variability from the mean. Its use is not limited to normal
distributions.


----- original message -----

"Jim Thomlinson" wrote in message
...
StdDev is a measurement of the variablility of the data so you are correct
in
assuming that there is no such thing as a negative StdDev. At 1 stddev
about
2/3 of your data points will lie within 1 std dev of the average. 2 std
dev
is around 95% of the data points. So the avearge temperature in January is
5
degrees +/- 3 degrees 67% of the time means that the temp is going to be
between 2 and 8 degrees most days. I would never calculate the -std dev as
it
will be the same as the positive. Not sure if that makes sense...

Post your data pints and your formulas if you are still having trouble...
--
HTH...

Jim Thomlinson


"PMK" wrote:

I have a spreadsheet with 52 data points on which I'm trying to identify
a +1
standard deviation and a -1 standard deviation. When I use the Excel
2003
std dev function to calculate the info, I get a negative standard
deviation
on the -1 calculation. Not being a stats wizard, I think it's
mathematically
impossible for a standard deviation to be negative. I am assuming I have
a
error in my data or formula. My data has a wide variation from a few
around
1500 to many 0 and some data in between. Any thoughts would be greatly
appreciated.