Thread: Random list of numbers (Excel 2002)

1. 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. 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.

3. 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.

4. 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. 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>

6. 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

Posting Permissions

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