software

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 amp; R or X Tilde amp; 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 amp; 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

quot;jimboquot; wrote:

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

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

quot;jimboquot; wrote:

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

quot;Arnequot; wrote:
gt; 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.

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

Well, 2*STDEV(...) gives you quot;2 sigmaquot; [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 quot;nquot; 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 quot;sigmaquot; 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 quot;std dev of the sample dataquot;, which is
sometimes used as to __estimate__ sigma. That is why
TINV() should be used to compute the quot;z-valuequot; (actually,
the t-value), not NORMSINV(). TINV() is also easier to
use for this purpose. I use 1e8 for quot;n-1quot; to approximate
the equivalent z-value (NORMSINV()).