sample size (Excel 2000 )

Topic

New Reply

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?

Reply | Quote

Replies

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

Peter

Reply | Quote

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 “Sample Size” +95% for the search on Google.

HTH,

Tom

Reply | Quote

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

Reply | Quote

What kind of coffee is in the cups.

zeddy

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Patty, here’s an answer from my Stats guru. He knows his statistics, but can’t remember his Lounge ID & password.

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…

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.

Reply | Quote