Neat random number generator (2000)

Topic

New Reply

Hi

I want to generate numbers 1 to 4 in random order eg:
1234
2341
3214
etc
each time the sheet is recalculated
I can do this but my solution is ugly and takes several ‘steps’. Due to some installation problems I cannot reliably use the extra excel functions (such as Ranbetween()) so have to stick with Rand() etc.

Any ideas?

Reply | Quote

Replies

Can the digits repeat? For example, is 1224 or 3333 valid?

Reply | Quote

No

Must always be 1234 but in different orders each time

Reply | Quote

Hi bob,
I wrote the following function for an exam generator (see post on preventing controls printing). I needed to randomly select questions from a list, without ever repeating a question and to randomise the order the answers appear, else the students would gradually associate the position of an answer to a given question. To produce a list of unique numbers in the range 1 to 4 call the function with MyVariant=RandList(4,4) and then step through the array assigned to MyVariant.

Function RandList(ByVal MaxListValue As Long, Optional ByVal ListSize As Long) As Variant

‘Produces a list of unique numbers, in the range 1 to MaxlistValue
‘The size of the list being 1 to ListSize or MaxListValue, whichever is smaller
‘The list is stored in ListArray
Dim ListArray() As Long
Dim AvailableValues() As Long
Dim counter As Long
Dim ListArraySize
Dim RandNumber As Long

‘Initialise AvailableValues to array of 1 to MaxListValue and fill array with values 1 to MaxListValue
ReDim AvailableValues(1 To MaxListValue) As Long
For counter = 1 To MaxListValue
AvailableValues(counter) = counter
Next

‘Determine size of array to be returned by function
If ListSize > MaxListValue Then
ListArraySize = MaxListValue
Else
ListArraySize = ListSize
End If
ReDim ListArray(1 To ListArraySize) As Long

For counter = 1 To ListArraySize
‘Get a RandNumber between 1 and upper limit of AvailableValues
Randomize
RandNumber = Int(UBound(AvailableValues) * Rnd + 1)

ListArray(counter) = AvailableValues(RandNumber)

‘Swap last item with selected item
AvailableValues(RandNumber) = AvailableValues(UBound(AvailableValues))

‘Shrink size of AvailableValues by 1 to get rid of used number
If UBound(AvailableValues) > 1 Then
ReDim Preserve AvailableValues(1 To UBound(AvailableValues) – 1)
End If
Next

RandList = ListArray
End Function

The function has the advantage of taking the same time for a given size of list. This could be important as list sizes increase and the size of list approaches or equals that of the available values. The disadvantage is memory used up as list size increases but i doubt this would be a factor in your case. Hope it helps,
Ewan

Reply | Quote

WOW!

Looks brilliant but I’m looking for something to do with relative novices. Either all within functions (as per my ugly method) or in very simple VBA if possible.

Reply | Quote

Hey i thought that was simple VBA!!!
It sounds as though you need to look into EVENT procedures. This is not as scary as it sounds. Use the VB tool bar to activate the editor and then double click your sheet name. This will bring up the code window on the right of the screen. At the top of the window there are two drop down boxes. The left hand one will probably have “(General)” and the right one will say “(Declarations)”. Bring down the left hand list and select “Worksheet”, the right hand one will probably change to “Selection Change” and the bare bones of a Worksheet_SelectionChange sub-routine will appear, feel free to delete it. Bring down the right hand list and select “Calculation”, now a Worksheet_Calculation procedure is created. Inside you fill in the procedure to something like:

Private Sub Worksheet_Calculate()
Dim MyVariant AS Variant

MyVariant=RandList(4,4)

‘Your code goes here
‘for example
Worksheets(“Sheet1”).Range(“A1”).Value = MyVariant(1)
‘will place the first value in MyVariant into A1 on sheet1

End Sub

And don’t forget to paste in my RandList function above the procedure!! The procedure will be run everytime the sheet is recalculated and a different random order of 1234 will be generated. You don’t say what you want with the array generated e.g. to be stuck in a cell as an array formula or the individual values placed in different locations. As an afterthought i use office 97 but i don’t imagine it’s much different for 2000 (this where 2000 users make a of me!!).
Ewan

Reply | Quote

If you put this list in the range A1:A24 on Sheet2:

1234
1243
1324
1342
1423
1432
2134
2143
2314
2341
2413
2431
3124
3142
3214
3241
3412
3421
4123
4132
4213
4231
4312
4321

Then you can use this formula where you want those random numbers:

=OFFSET(Sheet2!A1,ROUND(RAND()*(23-0)+0,0),0)

Reply | Quote

Legare,

=OFFSET(Sheet2!A1,ROUND(RAND()*(23-0)+0,0),0)

Pardon my dumb question, but what is the reason behind using 23-0 within the parentheses and then the +0 outside the parentheses?

Ken

Reply | Quote

I would have thought that round(rand()*23,0) would not produce a uniform distribution of integers between 0 and 23 as 0 and 23 would only appear about half as often as the other numbers.

I’ve always used int(rand()*24) + 1 to get a random integer between 1 and 24.

Reply | Quote

That was just to show the formula for generating a random number between two numbers. In this case, between 0 and 23. The zero in the formula is the lower end of that range. It is not needed for the formula and can be removed.

Reply | Quote

The students I’m teaching are New to Excel and have never seen VBA before and I don’t want to do any “Just type this in” exercises. I like your code and may well use it myself however.
What I’ve done is taken your basic idea of the array though.
Ive written out every variation of 1234 and indexed it eg:
1-1-2-3-4
2-1-2-4-3
3-1-3-2-4
4-1-3-4-2-
etc (it gives 24 variations)
I’ve then used Roundup(Rand()*24 ,0) to get a number between 1 and 24 and used 4 lookups to read in the appriopriate numbers – seems to work

Bob

Reply | Quote

I believe that your method using Roundup is going to produce a value between 0 and 24, not 1 and 24. Depending on how you are doing the lookup, it will either fail occasionally, or it will not produce a random result (the result will be biased toward the first entry in the list. Take a look at the solution I gave you which gets around that problem.

Reply | Quote

You’re right (of course) !

Much neater. I’ve not come across Offset before – handy.

Thanks for the tip

Bob

Reply | Quote

Another way of doing this, without necessarily using VBA, would be just by sorting a spreadsheet range. Enter the possible numbers in one column, with random numbers in the adjacent column. In your case that would give you a 4 x 2 range. You then sort the range based on the random number column, thereby randomly resequencing your numbers.
Don’t know if this is workable for you, but I always thought this was a neat solution.

Reply | Quote

Such a neat idea.

It works really well. Thank you – I think that this is THE answer.

I guess to be neat a bit ov VBA to do the sort but it could be a ‘recorded’ event.

Thnaks

Reply | Quote