Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Neat random number generator (2000)

    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?

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

    Re: Neat random number generator (2000)

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

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    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

  4. #4
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    No

    Must always be 1234 but in different orders each time

  5. #5
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    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.

  6. #6
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    Hey i thought that was simple VBA!!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    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 <img src=/S/clown.gif border=0 alt=clown width=15 height=15> of me!!).
    Ewan

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

    Re: Neat random number generator (2000)

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

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


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

    <pre>=OFFSET(Sheet2!A1,ROUND(RAND()*(23-0)+0,0),0)
    </pre>

    Legare Coleman

  8. #8
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    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

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

    Re: Neat random number generator (2000)

    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.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    You're right (of course) !

    Much neater. I've not come across Offset before - handy.

    Thanks for the tip

    Bob

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    Legare,

    =OFFSET(Sheet2!A1,ROUND(RAND()*<font color=d2691e>(23-0)+0</font color=d2691e>,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

  12. #12
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    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.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Neat random number generator (2000)

    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.
    Legare Coleman

  14. #14
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Neat random number generator (2000)

    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.

  15. #15
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Neat random number generator (2000)

    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

Posting Permissions

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