Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random list of numbers (Excel 2002)

    I have been asked to develop a random list of numbers that will be used by all students during the housing selection at our school. However, I do not know how to do that in Excel. Help?

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

    Re: Random list of numbers (Excel 2002)

    You can use the RANDBETWEEN function to generate random numbers. For example, if you place the following function:

    <pre>=RANDBETWEEN(1,100)
    </pre>


    it will generate a random number between 1 and 100. If you then fill it down for as many cells as you need random numbers, you will have a list. However, there may be duplicates in the list. If this is a problem, then you will need a VBA macro to generate the list.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random list of numbers (Excel 2002)

    Legare's reply is what I would use. I wasn't sure when the RANDBETWEEN function became available (Which version of Excel). So here's one other way...

    1. In a cell, you simply type: <font color=blue>=RAND()</font color=blue>. And tap the [ENTER} key.
    2. This will generate a random number in the selected cell. The number will be a decimal number between 0 and 1.
    3. You can see in the example that I've used: <font color=blue>=RAND()*100</font color=blue> to get a random number between 0 and 100.
    4. You will have to right-click on the cell, choose format cells, and put a zero in the decimal places to guarantee a clean, whole number.
    5. Then, you can just copy the cell to as many other cells as you like to get your list.

    Also, you should know that the list will change everytime the sheet recalculates. You'll need to let us know if you want the random list to remain constant.
    If you want the possibility of larger numbers, then change the number <font color=blue>100</font color=blue> in the formula to 1000 or 10000, etc.
    Attached Images Attached Images
    - Ricky

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random list of numbers (Excel 2002)

    I guess I did not ask the question well. I do not want to reuse any number. So if we have a range of 1 - 200, I would like all the numbers scrambled so I will have the same 1 - 200 but in random order. I got duplicates this way.

    Thanks.

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

    Re: Random list of numbers (Excel 2002)

    Here is a VBA macro that will generate a list of the integers between 1 and 200 in random order in Sheet1!A1:A200.

    <pre>Public Sub GenerateList()
    Dim iNum(1 To 200) As Integer, iMax As Integer
    Dim I As Integer, J As Integer, iRand As Integer
    For I = 1 To 200
    iNum(I) = I
    Next I
    Randomize
    iMax = 200
    For I = 1 To 200
    iRand = Int(iMax * Rnd + 1)
    Worksheets("Sheet1").Range("A1").Offset(I - 1, 0).Value = iNum(iRand)
    For J = iRand To iMax - 1
    iNum(J) = iNum(J + 1)
    Next J
    iMax = iMax - 1
    Next I
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random list of numbers (Excel 2002)

    No Need for VBA:
    1) In column A, create the numbers 1 to 200 in order. Enter 1 & 2, then auto fill to 200.
    2) in cell B1, enter the formula =RAND()
    3) double-click the square in the lower-right corner of B1. This auto-fills B1 thru B200.
    4) press F9 to recalculate
    5) Select columns A & B and use the Data | Sort menu to sort by column B
    6) Select & delete column B

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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