The Elsmar Cove Forum Does Excel STDEV calculate in 3 stdev (standard deviation)?
 Forum User Name Keep Me Logged In Password
 Register Photo Albums Blogs FAQ Registered Visitors Social Groups Calendar Search Today's Posts Mark Forums Read

 Elsmar Cove Forum Visitor Notice(s) The weekend is the Christian holiday of Easter. Things are typically slow in the forum on the weekends, this being a business related forum. If you ask a question and don't get an answer, please "Bump" your thread on Monday. We do try to "Bump" threads which aren't replied to but sometimes we miss one.

 Search the Elsmar Cove @import url(http://www.google.com/cse/api/branding.css); Custom Search Monitor the Elsmar Forum Monitor New Forum Posts Follow Marc & Elsmar Elsmar Cove Groups Sponsor Links Donate and \$ Contributor Forum Access Courtesy Quick Links Links that Elsmar Cove visitors will find useful in your quest for knowledge: Howard'sInternational Quality Services Marcelo Antunes'SQR Consulting Bob Doering'sCorrect SPC - Precision Machining NIST's Engineering Statistics Handbook IRCA - International Register of Certified Auditors SAE - Society of Automotive Engineers Quality Digest Portal IEST - Institute of Environmental Sciences and Technology ASQ - American Society for Quality
#1
2nd February 2007, 04:14 PM
 T Dutton 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?

#2
2nd February 2007, 05:24 PM
 Scott Catron True Artisan   Registration Date: Aug 2003 Location: Utah Posts: 662 Thanks Given to Others: 53 Thanked 182 Times in 131 Posts Blog Entries: 1 Karma Power: 112 Karma: 1309
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.
#3
2nd February 2007, 05:52 PM
 Howard Lee 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 Karma: 862
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!!!
#4
2nd February 2007, 06:33 PM
 Statistical Steven 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 Karma: 4184
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 n-1 and STDEVP divides by n. Can anyone tell me when you would use STDEVP?

__________________

Steven Walfish
#5
2nd February 2007, 09:14 PM
 Wesley Richardson 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 Karma: 885
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.
#6
3rd February 2007, 11:29 AM
 Steve Prevette Deming Disciple   Registration Date: Feb 2004 Location: Aiken, SC Posts: 2,260 Thanks Given to Others: 188 Thanked 1,290 Times in 705 Posts Karma Power: 293 Karma: 12873
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 n-1 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 n-1), the average of the STDEV's will converge on the population standard deviation.

__________________

Steve Prevette
"A Passionate Statistician", ASQ CQE, ASQ Fellow
#7
3rd February 2007, 10:10 PM
 Tim Folkerts 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 Karma: 5869
Re: Does Excel STDEV calculate in 3 stdev (standard deviation)?

I like what Steve said, but I want to make two minor points.
1. sigma (STDEVP) would be the population standard deviation, not an estimate. When you have the full set, you don't need to estimate!
2. 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.
#8
3rd February 2007, 11:32 PM
 Statistical Steven 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 Karma: 4184
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

 The Elsmar Cove Forum Does Excel STDEV calculate in 3 stdev (standard deviation)?

 Bookmarks

 Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)

 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 BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules

 Similar Discussion Threads Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote puck1263 Statistical Analysis Tools, Techniques and SPC 20 19th December 2012 12:17 AM Validation2011 Using Minitab Software 15 7th August 2011 05:07 AM amanbhai Statistical Analysis Tools, Techniques and SPC 4 6th February 2008 04:49 AM Abhijeet Statistical Analysis Tools, Techniques and SPC 3 11th July 2006 09:20 AM Richard-jxb Using Minitab Software 3 16th December 2003 10:51 AM

The time now is 01:42 AM. All times are GMT -4.