Results 1 to 8 of 8
  1. #1
    New Lounger
    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?

  2. #2
    3 Star Lounger
    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

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger
    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>

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: sample size (Excel 2000 )

    What kind of coffee is in the cups.

    zeddy

  6. #6
    4 Star Lounger
    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?

  7. #7
    4 Star Lounger
    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 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. #8
    Silver Lounger
    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 (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>
    <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>

Posting Permissions

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