Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Standard Deviation?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Standard Deviation?
Fascinating!
If something like: =STDEV(A1:A26) is coming out negative, please post the input data. -- Gary''s Student - gsnu200909 "PMK" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Standard Deviation?
PMK wrote:
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. 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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Standard Deviation?
Gary''s Student wrote:
Fascinating! If something like: =STDEV(A1:A26) is coming out negative, please post the input data. That would be fascinating, but I don't think it's possible. I believe he meant that "mean minus one standard deviation" was negative. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
standard deviation | Charts and Charting in Excel | |||
Standard Deviation | Excel Discussion (Misc queries) | |||
standard deviation | Excel Discussion (Misc queries) | |||
Standard Deviation | Excel Worksheet Functions | |||
standard deviation | Excel Discussion (Misc queries) |