Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sample Total (2000)

    The following line is from a sub that pulls a thousand-person sample stratified by state from a larger list. My problem involves the part of the routine that generates the number of people to pull from each state. The sub loops through a table and reads the number of people in each state (intStateDCCnt(i), and then figures the count to use using this formula (where lngDCTotal is set to the record count for the table with the entire population).

    !Cnt2Use = CInt(intStateDCCnt(i) / lngDCTotal * 1000)

    My problem is that after looping through all the states I end up with a total count of 1005, not 1000. I suspect an error with rounding. Is there a better function to use than CInt?

    Kevin

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

    Re: Sample Total (2000)

    CInt rounds to the nearest integer. This leads to rounding up in some cases, rounding down in others. You can't predict in advance how the rounding errors add up. You'll have to pass through the records twice; in the second pass decrease Cnt2Use for the records with the largest relative rounding error until you arrive at the correct total.

    Note: statistical applications such as SPSS are much more suitable for something like this. They have built-in sampling routines that take care of things like this with a single interactive command.

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sample Total (2000)

    I'm sorry, how do I know which which records have the largest relative rounding error?

    Kevin

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

    Re: Sample Total (2000)

    You need to add a calculated field to the recordset you open:
    Abs(CInt(intStateDCCnt(i) / lngDCTotal * 1000) - intStateDCCnt(i) / lngDCTotal * 1000) As RoundingError
    and sort (descending) on this expression. Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sample Total (2000)

    Well, it's a little clearer than mud. Thanks once again, Hans.

    Kevin

Posting Permissions

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