


2nd February 2007, 04:14 PM

Registered Visitor
Registration Date: May 2005
Location: USA New Hampshire


Posts: 10
Thanks Given to Others: 0
Thanked 0 Times in 0 Posts
Karma Power: 38 Karma: 10


Does Excel STDEV calculate in 3 stdev (standard deviation)?
Does Excel STDEV calculate in 3 stdev?

2nd February 2007, 05:24 PM


True Artisan
Registration Date: Aug 2003
Location: Utah


Posts: 662
Thanks Given to Others: 53
Thanked 182 Times in 131 Posts
Karma Power: 112


Re: 3 standard Deviation in excel
If I'm interpreting the questions correctly  NO.
STDEV is one standard deviation  you'll have to multiply it by 3 in a formula somewhere.
__________________
I don't have a solution, but I admire your problem.  Steven Wright
I'm a moderator here, so if you need help with something, feel free to ask.

2nd February 2007, 05:52 PM


Involved in Discussions
Registration Date: Sep 2006
Location: South Carolina
Age: 51


Posts: 107
Thanks Given to Others: 42
Thanked 60 Times in 33 Posts
Karma Power: 42


Re: 3 standard Deviation in excel
In Excel STDEV yeilds one sample standard deviation. To get 3 sigma you need to multiply it by 3. Also, if you need the standard deviation of a population, you should use STDEVP instead.
__________________
Rock is hard but water is patient!!!

2nd February 2007, 06:33 PM


Statistician
Registration Date: May 2005
Location: New Jersey
Age: 48


Posts: 963
Thanks Given to Others: 229
Thanked 351 Times in 244 Posts
Karma Power: 141


Re: 3 standard Deviation in excel
Quote:
In Reply to Parent Post by Howard Lee
In Excel STDEV yeilds one sample standard deviation. To get 3 sigma you need to multiply it by 3. Also, if you need the standard deviation of a population, you should use STDEVP instead.

I wish Microsoft would do away with the STDEVP function. It has no use. The difference is that STDEV divides by n1 and STDEVP divides by n. Can anyone tell me when you would use STDEVP?
__________________
Steven Walfish
When in doubt, ask your company statistician!

2nd February 2007, 09:14 PM


Wes R
Registration Date: Jun 2004
Location: Indiana, USA
Age: 69


Posts: 162
Thanks Given to Others: 3
Thanked 57 Times in 30 Posts
Karma Power: 58


Re: 3 standard Deviation in excel
Quote:
In Reply to Parent Post by Statistical Steven
Can anyone tell me when you would use STDEVP?

Hi Statistical Steven,
It would be used if you had the entire data set. Although this is less common, there are a few cases: 1. The standard deviation of test scores for the entire number of students in a class. 2. The standard deviation of a part characteristic when you have measured all of the parts in the production run. Both cases would typically have a fairly small number in the population, such as less than 100 items.
I accept that if you have measured all of the population values, then you could graph the values and see how they are distributed.
Wes R.

3rd February 2007, 11:29 AM


Deming Disciple
Registration Date: Feb 2004
Location: Aiken, SC


Posts: 2,265
Thanks Given to Others: 189
Thanked 1,298 Times in 710 Posts
Karma Power: 293


Re: 3 standard Deviation in excel
Quote:
In Reply to Parent Post by Statistical Steven
I wish Microsoft would do away with the STDEVP function. It has no use. The difference is that STDEV divides by n1 and STDEVP divides by n. Can anyone tell me when you would use STDEVP?

I think I've also answered this on the ASQ board from a question there. But here goes:
The formula for sigma (STDEVP) is the maximum likelihood estimator for the population standard deviation. However, it is a biased estimator. If you only have a sample of the population (and consider  if the "future" is part of your population, you still only have a sample), STDEVP will have a bias in it. If you repeatedly sample from a population, you will notice that the average STDEVP values will not tend to match the actual population standard deviation. However, if you use STDEV (dividing by n1), the average of the STDEV's will converge on the population standard deviation.
__________________
Steve Prevette
"A Passionate Statistician", ASQ CQE, ASQ Fellow

3rd February 2007, 10:10 PM


Super Moderator
Registration Date: Sep 2003
Location: Kansas, USA
Age: 51


Posts: 975
Thanks Given to Others: 29
Thanked 364 Times in 217 Posts
Karma Power: 152


Re: Does Excel STDEV calculate in 3 stdev (standard deviation)?
I like what Steve said, but I want to make two minor points.  sigma (STDEVP) would be the population standard deviation, not an estimate. When you have the full set, you don't need to estimate!
 I believe it is actually (STDEV)^2 that averages to (STDEVP)^2, not STDEV that average to STDEVP. That is, you take the variances calculated from the sample standard deviations and average them to get an unbiased estimate of the true variance.
Of course, all this is all WAY to esoteric for most people!
__________________
To wonder is to begin to understand.

3rd February 2007, 11:32 PM


Statistician
Registration Date: May 2005
Location: New Jersey
Age: 48


Posts: 963
Thanks Given to Others: 229
Thanked 351 Times in 244 Posts
Karma Power: 141


Re: 3 standard Deviation in excel
Actually S^2 is the MLE for sigma squared...but that is not the point. If as Wes stated, you have the entire population, then why would you need to know the standard deviation. That is to say, any inferences to be made can be made from the data in the population. There are no confidence intervals that need to be calculated. I am stumped, regardless of the theoretical differences between sigma and S, why there is an application that someone would be using Excel for that requires the population stdev.
__________________
Steven Walfish
When in doubt, ask your company statistician!

Lower Navigation Bar


Do you find this discussion thread helpful and informational?
Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)


Thread Tools 
Search this Thread 


Display Modes 
Rate Thread Content 
Linear Mode


Forum Posting Settings

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off




