Sample Size Calculator in Excel

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

To calculate a sample size you need to know:

1. The confidence level required (90%, 95%, 99%)
2. The desired width of the confidence interval
3. The variability of the characteristic (e.g., mean)

What's Cool About the QI Macros Sample Size Calculator?

The QI Macros Sample Size Calculator works with both variable (measured) and attribute (counted) data.

Just click on the QI Macros menu, then Anova and Analysis Tools and then Sample Size Calculator. you should see the following:

The defaults are set to standard parameters but can be changed:

• 90% confidence level
• +0.05 confidence interval (1/2 of desired width)
• Variable data with standard deviation of (0.167)
• Attribute data with percent defects of 50%

The Population is also an input area as evidenced by its yellow shading.

Confidence Level
In sampling, you want to know how well a sample reflects the total population. The 95% confidence level means you can be 95% certain that the sample reflects the population within the confidence interval.

Step 1 - Select a confidence level
(Typically 95%)

Confidence Interval
The confidence interval represents the range of values which includes the true value of the population parameter being measured.

Step 2 - Set the confidence interval
(e.g., + .05)

Step 3 - Attribute data - Set percent defects to 0.5

If 95 out of 100 are good and only 5 are bad, then you wouldn't need a very large sample to estimate the population. If 50 are bad and 50 are good, you'd need a much larger sample to achieve the desired confidence level. Since you don't know beforehand how many are good or bad, you have to set the attribute field to (50% or 0.5).

Step 3 - Variable Data - Enter Standard Deviation
If you know the standard deviation of your data (from past studies), then you can use the standard deviation.

If you know the specification tolerance, then you can use (maximum value - minimum value)/6 as your standard deviation. (The default is 1/6=.167).

Step 4 - Enter the total population (if known)

Using the default values (95%, +.05, Stdev=0.167), you would get:

Step 5- Read the Sample Size
Use the sample size calculated for your type of data: Attribute or Variable.

Variable Sample Size: If we are using variable data the sample size would be 43.

Attribute Sample Size: What if you were using attribute data, (e.g., counting the number of defective coins in a vat at the Denver Mint)?

Here you'd need 384 coins to be 95% confident that the coins fell within the 5% interval.

Attribute Example

What if you knew there were 1000 coins in the vat (population known)?

You only need 278 to be confident.

What if you changed the confidence interval to be +0.1)?

You only need 88 to be 95% confident.

Variable Example

A sample must be selected to estimate the mean length of a part in a population. Almost all production falls between 2.009 and 2.027 inches.

Estimated standard deviation = (2.027-2.009)/6 = .003

And you want to be 95% confident that the sample is within +/-0.001 of the true mean. Enter the data as shown below:

You need 35 samples.

Watch this Sample Size Calculator Video

The Sample Size Calculator is just one of the tools included in the QI Macros for Excel SPC Software for Excel.

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

It's Easy to Calculate Sample Size Using the QI Macros for Excel Start Now

Download
QI Macros
30-Day Trial Now!

Or Buy It Now!