Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sampling without replacement (2002)

    I am trying to draw a sample of dates without replacement (no date can be drawn more than once). I have tried the analysis tools add-on sampling function but it does it with replacement. I have also tried the analysis tools add-on random number generator function (where I tell it to generate random dates based on my list of dates) but still I have the replacement problem. To check the resulting list of dates for duplicates I ended up using conditional formatting to yellow highlight the cell if it is identical to cell above, then sorting by date , deleting the highlighted duplicates and then sorting back to the original random order in which they were drawn. Problem is this approach is time consuming (I have 48 different samples I have to draw).

    Is there a way to draw a sample of dates without replacement short of writing a program in visual basic? Thanks!

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

    Re: sampling without replacement (2002)

    Say you have dates in A1:A1000
    In B1, enter =RAND()
    Fill down by double clicking the fill grip in the lower right corner of B1.
    Sort on column B
    Select and copy the top n cells in column A.

    Each time the sheet is recalculated, the random numbers will change.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sampling without replacement (2002)

    Another idea similar to Hans' Suggestion:
    (I put the randon numbers before the dates for Vlookup)
    Say you have dates in B1:B1000
    In A1, enter =RAND()
    Fill down by double clicking the fill grip in the lower right corner of A1.

    Now if you want 48 random dates without replacement, in D148, enter in D1:
    <pre>=VLOOKUP(LARGE($A$1:$A$1000,ROW()),$A$1:$B$10 00,2,FALSE)</pre>


    Copy D1 to D248

    Now <f9> will constantly give you a new sample of dates. No need to sort at all or even copy/paste.

    Column A could be hidden if desired. Or if you wanted to put it onto another sheet, instead of Vlookup use, match and index combination.
    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sampling without replacement (2002)

    Depending on exactly what you are trying to do, the code Here can be adapted to do it.

    Legare
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sampling without replacement (2002)

    As I see it, it is going to require VBA. The methods discussed by Hans, Steve and Legare do not address the duplicate date problem you are trying to avoid.
    Someone that is better than I am with VBA (just about anyone in the lounge) could write a macro that would do this making sure he dates are unique.
    Or, maybe I missed the point.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sampling without replacement (2002)

    Mine and Hans do not produce duplicates. Steve's solution has a small possibility of doing so.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sampling without replacement (2002)

    Legare,
    To Legare, Hans and Steve -MY BAD. I misunderstood and now have egg on my face. I thought there were duplicate dates in the raw data and the random number generator could still give some duplicates. But, this is not the case.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sampling without replacement (2002)

    If the small possibility of dup random number is an issue, you could use something like
    =int(100000*rand())+row()/100000

    Instead of

    =rand()

    which should give no dupes (but is probably not entirely random (though I don't know if the rand function is entirely random either)

    Steve

Posting Permissions

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