Results 1 to 8 of 8
Thread: sample size (Excel 2000 )

20020411, 17:51 #1
 Join Date
 Mar 2002
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20020411, 18:08 #2
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20020411, 23:27 #3
 Join Date
 Aug 2001
 Location
 Seattle, Washington, USA
 Posts
 50
 Thanks
 0
 Thanked 0 Times in 0 Posts

20020412, 14:05 #4
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
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?
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20020412, 15:42 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,957
 Thanks
 140
 Thanked 526 Times in 501 Posts
Re: sample size (Excel 2000 )
What kind of coffee is in the cups.
zeddy

20020412, 17:21 #6
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20020414, 17:37 #7
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 builtin functions to use), but sample sizes for counts and proportions are based on other distributions (poisson, hypergeomdist, binomdist are Excel's builtin 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: (10097.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.

20020415, 12:20 #8
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
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 (n9) 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 straightforward, 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><font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>