Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Statistical Sampling (2002 SP3)

    Edited by HansV to present data in table format

    I want XL to tell me, based on the following table, how many records are needed for a statistical sample. I was going to try and use nested IF statements to get the it to return the sample size, based on the "universe", the total of records.

    <table border=1><tr><td>No. in Universe</td><td>Min. Sample</td><td align=right>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Sampling (2002 SP3)

    Reformat your table like this:

    <table border=1><td></td><td align=center>F</td><td align=center>G</td><td align=center>H</td><td align=center valign=bottom>1</td><td valign=bottom>Size</td><td valign=bottom>MinSample</td><td valign=bottom>MaxSample</td><td align=center valign=bottom>2</td><td align=right valign=bottom>0</td><td align=right valign=bottom>20</td><td align=right valign=bottom>20</td><td align=center valign=bottom>3</td><td align=right valign=bottom>99</td><td align=right valign=bottom>20</td><td align=right valign=bottom>40</td><td align=center valign=bottom>4</td><td align=right valign=bottom>299</td><td align=right valign=bottom>40</td><td align=right valign=bottom>54</td><td align=center valign=bottom>5</td><td align=right valign=bottom>499</td><td align=right valign=bottom>54</td><td align=right valign=bottom>69</td><td align=center valign=bottom>6</td><td align=right valign=bottom>749</td><td align=right valign=bottom>69</td><td align=right valign=bottom>82</td><td align=center valign=bottom>7</td><td align=right valign=bottom>999</td><td align=right valign=bottom>82</td><td align=right valign=bottom>122</td><td align=center valign=bottom>8</td><td align=right valign=bottom>1999</td><td align=right valign=bottom>122</td><td align=right valign=bottom>152</td><td align=center valign=bottom>9</td><td align=right valign=bottom>2999</td><td align=right valign=bottom>152</td><td align=right valign=bottom>192</td><td align=center valign=bottom>10</td><td align=right valign=bottom>4999</td><td align=right valign=bottom>192</td><td align=right valign=bottom>267</td><td align=center valign=bottom>11</td><td align=right valign=bottom>10000</td><td align=right valign=bottom>267</td><td align=right valign=bottom>267</td></table>
    and use this formula to extract the sample size :
    (Table is in F2 to H12, population size in I2)

    =VLOOKUP(I2,F2:H12,2,TRUE)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Statistical Sampling (2002 SP3)

    See attached workbook (I interpreted the problem slightly differently than Jan Karel, but the method is similar)

  4. #4
    New Lounger
    Join Date
    Mar 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Sampling (2002 SP3)

    This is wonderful I started with a LOOKUP but couldn't make it work. You guys make it look so simple. Thank you very much.

Posting Permissions

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