Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Numbers (2003)

    Hi Loungers - My question is this. I'm using a =RANDBETWEEN(5,55). Is there away to stop the random number generation once it has generated them the first time and/or is there away to force the generation with a command after the first generation?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts

    Re: Random Numbers (2003)

    Functions involving RAND or RANDBETWEEN will generate a new random number whenever the workbook is recalculated, unless you set calculation to Manual in the Calculation tab of Tools | Options. If you do that, you can still force a recalculation (and hence new random numbers) by pressing F9.

    If you want to freeze the numbers definitively, copy them to the clipboard and do a paste special > values. This will remove the formulas, so the values will not change any more.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random Numbers (2003)

    If you do not mind using VBA code then the following user defined function may assist. Once it calculates a value it will not change unless you request a full recalculation by pressing Ctrl+Alt+F9

    The function has an optional 3rd arguement which specifies how many decimal places you want the result to be. If missing the result is an integer

    =RANDOMBETWEEN(5,55) - generates a random number between 5 & 55
    =RANDOMBETWEEN(5,55,2) - generates a random number between 5 & 55 with 2 decimal places

    <code>Public Function RandomBetween(Lowest As Long, Highest As Long, Optional Decimals As Integer)</code>
    <code> If IsMissing(Decimals) Or Decimals = 0 Then</code>
    <code> Randomize</code>
    <code> RandomBetween = Int((Highest + 1 - Lowest) * Rnd + Lowest)</code>
    <code> Else</code>
    <code> Randomize</code>
    <code> RandomBetween = Round((Highest - Lowest) * Rnd + Lowest, Decimals)</code>
    <code> End If</code>
    <code>End Function</code>
    <code></code>

  4. #4
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (2003)

    Great - Thanks Hans

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (2003)

    Tony,

    Thanks for the reply - will give it a shot - One question though, how do I specify which cells I want the random numbers generated in?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts

    Re: Random Numbers (2003)

    Put the function Tony posted in a standard module, and enter a formula like <code>=RANDOMBETWEEN(5,55)</code> in each cell that should contain a random number.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (2003)

    Thanks again Hans

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Random Numbers (2003)

    Hans, will this work in Access as well? Having alittle trouble with getting the field in a form to have the number generation.

    Thanks in advance.
    KST

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts

    Re: Random Numbers (2003)

    Access doesn't have RAND or RANDBETWEEN.

    You can use the VBA function Rnd instead of RAND, for example by entering the following formula in the Control Source property of a text box on a form:

    =10*Rnd()

    You can also use Rnd to define a calculated field in a query, but if you use something like this:

    RandomValue: Int(10*Rnd()+5)

    you will get the same value in all records. To return a different value in each record, provide a number field as argument to Rnd:

    RandomValue: Int(10*Rnd([ID])+5)

    where ID is the name of a number field.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Random Numbers (2003)

    Thank Hans.... I'm trying to do random numbers between 1 and 900. I'm using the query design you recommending as a 'Make-Table Query'. I'm getting duplicate numbers. I don't want duplicate numbers....
    What cha think?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts

    Re: Random Numbers (2003)

    We're straying too far from Excel now. I have started a new thread in the Access forum with a possible solution: <post:=724,805>post 724,805</post:>. Please post replies there.

  12. #12
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random Numbers (2003)

    Try the formula way in Excel,

    To do random numbers between 1 and 900, without duplicate numbers

    1] A1, enter this formula :

    =CEILING(RAND()*9,1)

    2] A2, enter this array formula ( confirmed by Shift+Ctrl+Enter ) and copied down :

    {=SMALL(IF(ISNA(MATCH(ROW($1:$900),$A1:A1,)),ROW($ 1:$900)),CEILING(RAND()*(901-COLUMN()),1))}

    Regards
    Bosco

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts

    Re: Random Numbers (2003)

    Hi Bosco,

    I like your array formula, it's clever. But <!profile=KSTidmore>KSTidmore<!/profile> asked how to do this in Access.

Posting Permissions

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