Results 1 to 8 of 8

Thread: Random number

  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Let's say I have a column of 20 numbers, each one in a separate cell.

    How do I choose X (say 6) random numbers from such a set? (in Excel 2007).

    I poked around in the function list but nothing seems to do what I want.

  2. #2
    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
    If you range is A1:A20 you can put the formula in a cell:

    =INDEX($A$1:$A$20,20*RAND()+1)

    And copy it 6 times and you will get 6 random elements from the list.

    Steve

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by SteveA View Post
    If you range is A1:A20 you can put the formula in a cell:

    =INDEX($A$1:$A$20,20*RAND()+1)

    And copy it 6 times and you will get 6 random elements from the list.

    Steve
    Hi Steve,

    The problem with a formula-based approach is that the same sumber could be repeated two or more times.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    This thread may offer some useful tips and links.....
    http://bro.ws/776957L

  6. #6
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by macropod View Post
    Thanks for that pointer Macro. The tip you pointed to was complex and did not do exactly what I wanted. So I did a search there and came up with this, which does exactly what I want!

    http://www.excelforum.com/excel-gene...generator.html

  7. #7
    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
    The problem with a formula-based approach is that the same number could be repeated two or more times.
    But that is the nature of what you asked for: "How do I choose X (say 6) random numbers from such a set?"

    Pulling 6 out of 20 possible items, if they are truly random may yield some repeats. If you want 6 unique items then you do not want a random sample...

    If you want 6 items with no repetitions: you can have your list of 20 items in B1:B20
    The in In A1, enter:
    =RAND()
    Fill down by double clicking the fill grip in the lower right corner of A1 to put the formula in A1:A20

    Now if you want 6 random items without replacement, in D16, enter in D1:
    =VLOOKUP(LARGE($A$1:$A$20,ROW()),$A$1:$B$20,2,FALS E)

    Copy D1 to D26

    Now <F9> will constantly give you a new sample. 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


  8. #8
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Steve - I was negligent in not specifying that I did not want repeats.

    Your solution appears to be somewhat similar to the link to the solution in Excel tips that I posted above.

    There is always more than one way to skin that old cat!

Posting Permissions

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