Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Angry Unanswered: random number generator

    I am trying to get excel to generate random numbers. When I write the formula =randombetween(1,28000) it returns one number between 1 and 28,000 but I need 400 unique numbers between 1 and 28,000. What we are trying to do is using all available parcels in our community and there are 28,000 of them we need to pick 400 of them to analyze. We thought the easiest way was to copy and paste all 28,000 parcels into excel and assign them a number between 1 and 28000 and then pick 400 to analyze. Any suggestions to have excel return 400 unique random numbers between 1 and 28000 thanks for the help!

  2. #2
    Join Date
    Oct 2003
    Select the 28000 cells that you will be using, type in your formula, then hit CTRL + ENTER, and it will populate all 28000 cells.

    But keep in mind that everytime you do something in Excel, it will regenerate the numbers. Therefore, immediately after you do this, copy, paste special, values.
    Last edited by shades; 03-10-04 at 14:24.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    This isn't really easy to do in Excel due to the unique constraint. It can be done though. The basic process boils down to:

    1) Begin loop with 400 iterations to go
    2) Pick a row
    3) If it is already "tagged", move up or down through the list to the first untagged row
    4) tag the current row
    5) decrement the loop counter, and repeat if not zero

    Is it possible to use Access instead of using Excel, because that would make it a lot easier?


  4. #4
    Join Date
    Mar 2004
    I found an easy way to do this, so I solved my own question! First you go to tools and choose add-ins then you choose the Analysis tool pack and click ok. Then go back to tools and choose Data Analysis and choose random number generator. Then simply enter the paramaters if you don't know what they are just click on help and it gives ample description then once you have entered the things you need it does just that, Very quickly it generated 400 different numbers between 1 and 28000. easy as pie!!!

Posting Permissions

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