Does Excel STDEV calculate in 3 stdev?

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.

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.

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?

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.

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.

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! :lol:

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.

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.

The value I see for sigma (the population standard deviation or STDEVP) is that it is still a good description of the spread of the data.

It is the same as asking why calculate the mean of a full population. If you have all the individual data points, then you don't need to know the mean.

Each measurement is a useful summary of the data. The mean doesn't tell you what each value is, but it does tell you roughly what to expect. Similarly, the standard deviation doesn't tell you how far each value is from the mean, but it does tell you roughly what to expect. No summary is as complete as the full data set, but this two number summary is pretty good to describe the main features of a set of data.


Tim

Each measurement is a useful summary of the data. The mean doesn't tell you what each value is, but it does tell you roughly what to expect. Similarly, the standard deviation doesn't tell you how far each value is from the mean, but it does tell you roughly what to expect. No summary is as complete as the full data set, but this two number summary is pretty good to describe the main features of a set of data.

And this is the conclusion Dr. Shewhart came to in Economic Control of Quality of Manufactured Product.

In most operational cases, we have only a sample - as we are trying to predict future performance (which is part of the population) from the existing data.

Thanks all for the information. I had an idea that excel was only going out 1 srdev.

Tim

As Tim said STDEVP is a biased estimate, Thanks Wes R. to what is the use for....

Returning to the original question, Excel STDEV calculate in 3 stdev
As is been said NO, and I must say that the STDEV is no good estimate for variation in a control chart (as the name of the post seems to imply), the STDEV is the total variation estimate, that estimate is too much affected by outliers, beware:caution: , use the constants and estimates for the within subgroup variation used for control charts, as Donald W. said (more or less) The important thing is to use the estimate to it's purpouse

Returning to the original question,
As is been said NO, and I must say that the STDEV is no good estimate for variation in a control chart (as the name of the post seems to imply), the STDEV is the total variation estimate, that estimate is too much affected by outliers, beware:caution: , use the constants and estimates for the within subgroup variation used for control charts, as Donald W. said (more or less)

I have found the STDEV to be a good estimate for control charting, which does fly in the face of Dr. Wheeler's writings. Yes, it is much more sensitive to an outlier than the moving range. But, I have done both calculations in several real situations, and the two results match well AS LONG AS you have identified a stable time interval. STDEV has the advantage that it is directly calculable in Excel, the average moving range takes a bit more work.