Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
PMK PMK is offline
external usenet poster
 
Posts: 2
Default Negative Standard Deviations?

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.


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Negative Standard Deviations?

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Negative Standard Deviations?

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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Negative Standard Deviations?

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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 905
Default Negative Standard Deviations?

"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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How many Standard Deviations above and below the mean each number is. [email protected] Excel Worksheet Functions 4 July 14th 07 12:00 PM
Plotting means and standard deviations I already have kimhelms Charts and Charting in Excel 4 November 21st 06 05:10 PM
Calculate 2 Standard Deviations Michael Excel Worksheet Functions 2 April 17th 06 06:13 PM
Graph Standard Deviations ed Excel Discussion (Misc queries) 1 August 3rd 05 09:14 PM
Standard deviations in Excel Fred Zack Charts and Charting in Excel 2 June 15th 05 01:19 AM


All times are GMT +1. The time now is 04:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"