# Thread: sample size (Excel 2000 )

1. ## sample size (Excel 2000 )

Ok, it has been a long time since college, but I need the formula for selecting a sample size with say 95% assurance. For example, I have 1000 coffee cups, I need 95% assurance that all the cups hold more than 1 cup of liquid. What size sample should I select and test to achieve 95% assurance?

2. ## Re: sample size (Excel 2000 )

Not sure what sample size you need but should be a neat trick getting more than 1 cup of liquid in a cup in the first place <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Peter

3. ## Re: sample size (Excel 2000 )

Patty,

Our friend Google gave a hit for this site, among many others. It says it is an online calculator for sample size, etc.

FWIW, I used <font color=blue>"Sample Size" +95%</font color=blue> for the search on Google.

HTH,

Tom

4. ## Re: sample size (Excel 2000 )

Patty, been too long for me too, but does either of the Excel functions, NEGBINOMDIST or HYPGEOMDIST, do what you want?

5. ## Re: sample size (Excel 2000 )

What kind of coffee is in the cups.

zeddy

6. ## Re: sample size (Excel 2000 )

Patty,

Can you please rephrase your question? Do you want to take a sample from the 1000 cups, control that sample and based on the obtained results say something about the complete lot? Or do you mean something different?
Typical question in quality control is: what is the sample size I need to test from a much larger lot of products, given an acceptable quality level (AQL of say 1%), with a producer's risk of 5% and a consumer's risk of 10%. An on top of that, how many defects should I allow in the sample to accept the whole batch?

7. ## Re: sample size (Excel 2000 )

If I understand your question and based on Sammy's last post where he talks about volumes, then I would say that you can be 100% sure that all the cups hold more than one cup as soon as the accumulated volume of the sample exceeds the maximum volume of one cup. So, take one cup at a time, measure its volume, then take another cup, measure its volume again, check if the sum of volumes exceeds the maximum volume of one cup; if not, then take another sample etc. Your sample size can be 2 (the minimum) up to the complete batch if the cups do not contain that much.

There are quite many formulas to calculate sample sizes, but it should be clear if we are talking about continuous variables (like the volume of a cup) or about counts (like the number of cups). The approach is quite different. Sample size calculations for continuous variables are indeed based on standard deviations and average values (the normal distribution; normdist and tdist are Excel's built-in functions to use), but sample sizes for counts and proportions are based on other distributions (poisson, hypergeomdist, binomdist are Excel's built-in functions that might help here).

e.g. the 1.96 value Sammy is using, corresponds to NORMINV(0.975, 0, 1) which is the value corresponding to a probability of 97.5% (the normal distribution is symmetrical, so to obtain a 95% confidence, you should use the 97.5% probability in the NORMINV function: (100-97.5%) + 2.5% = 5%. Using the standard normal distribution (meaning average = 0 and standard deviation = 1) you obtain 1.96.
The formula of Sammy gives the absolute minimum sample size for this kind of calculations.

8. ## Re: sample size (Excel 2000 )

Patty, here's an answer from my Stats guru. He knows his statistics, but can't remember his Lounge ID & password. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

<img src=/w3timages/blackline.gif width=33% height=2>
If you know the standard deviation of the population of coffee cup volumes,
the formula you need is:

n =(t*s/d)^2 (round up)

where: t=1.96 for 95% confidence
s is the standard deviation of the population of coffee cup volumes
d is the difference between the average cup volume and one cup

If you don't know s, measure nine cups to get an estimate of s. Then, if
n<= 9 you are done. Otherwise measure (n-9) more cups, and resolve with a
new estimate of s. Repeat until you have measured enough. Of course, the
measured cups must be randomly selected from the population.

If the average cup volume is less than one cup, they aren't cups...
<img src=/w3timages/blackline.gif width=33% height=2>

This is Sam again. Well, it looks straight-forward, but how does 95% confidence relate to t=1.96? Guess I took too many naps, in my Stats class. <img src=/S/snore.gif border=0 alt=snore width=32 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•