STDEV() returns 1 sigma answer which represents 68% of the data.
How do I get STDEV() 2 sigma which represents 95% of the data?

stdev times 2 = 2 (4sigma)
stdev times 3 = 3 (6sigma)

The following calculations of estimated standard deviation are used in the
calculation of capability indices for control charts:

Sigma Based on the Range:
Sigma(R)=R/d2

where R is the average subgroup range from the XBar & R or X Tilde & R
Chart and d2 is the standard SPC constant based on the subgroup size.

Sigma Based on the Subgroup Standard Deviation:

Sigma(S)=S/c4

where S is the average subgroup standard deviation from the XBar & S Chart
and c4 is the standard SPC constant based on the subgroup size.

Sigma Based on the Paired Range:
Sigma(PR=PR/d2
where PR is the average subgroup range from the RX Chart and d2 is the
standard SPC constant using subgroup size 2.

Sigma Based on the Moving Range:
Sigma(MR2)=MR/d2

where MR is the average subgroup range from the XMR(2) Chart and d2 is the
standard SPC constant using subgroup size 2.

where MR is the average subgroup range from the XMR(3) Chart and d2 is the
standard SPC constant using subgroup size 3.

Hope this helps

"jimbo" wrote:

STDEV() returns 1 sigma answer which represents 68% of the data.
How do I get STDEV() 2 sigma which represents 95% of the data?

When you have a data set and calculate the stdev, that value is the Sqrt of
the variance of the data.

If you assume the data is normally distributed, then the stdev is an
estimate of sigma. The formula Z = (X-mu)/sigma transforms your data set to
the standard normal distribution. For a standard normal data set, the
probability that the value of a random variable occurrs between -2 and 2 is
~95%.

Remember the distribution is symmetrical about 0.

NORMDIST(2) = .97725 and thus, NORMINV(.97725,0,1) = 2

NORMINV(.95,0,1) = 1.644854

NORMINV(.97725,0,stdev) will give the 2 sigma for your data set, but this is
not the way to estimate the 95% confidence interval.

stdev is a calculation on the data set based on the definition of variance.
sigma is a statistical parameter in a family of normal distributions
(standard normal implies sigma equals 1)



Arne

"jimbo" wrote:

STDEV() returns 1 sigma answer which represents 68% of the data.
How do I get STDEV() 2 sigma which represents 95% of the data?

"Arne" wrote:
Remember the distribution is symmetrical about 0.

Only if the mean is zero. That is true for the standard
normal distribution. It is not necessarily true for other
normal distributions.

"jimbo" wrote:
STDEV() returns 1 sigma answer which represents
68% of the data. How do I get STDEV() 2 sigma
which represents 95% of the data?

Well, 2*STDEV(...) gives you "2 sigma" [1]. But the mean
+/- 1.96*STDEV(...) represents 95% of the data, assuming
a normal distribution.

More correctly, the mean +/- TINV(1-95%,n-1)*STDEV(...)
represents 95% of the data, where "n" is the sample size.

Caveat: STDEV() is a measure of dispersion of the data. It
is used for all types of distributions, among other measures
of dispersion. But mean +/- 1.96*STDEV(...) covers 95% of
the data only if the data has a normal distribution.


-----
[1] Technically, the term "sigma" refers to the population std
dev, which is STDEVP(). But usually we do not know have
all the data for the population; we only have sample data.
So STDEV() is the "std dev of the sample data", which is
sometimes used as to __estimate__ sigma. That is why
TINV() should be used to compute the "z-value" (actually,
the t-value), not NORMSINV(). TINV() is also easier to
use for this purpose. I use 1e8 for "n-1" to approximate
the equivalent z-value (NORMSINV()).