Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RANDBETWEEN-excel xp (sp-1)

    When I use the function RANDBETWEEN in xcel-xp I can generate a random number in the chosen cell, when I move to a new cell and generate a new random number a new number is generated in the previous cell. How can I generate discrete random numbers in each chosen cell without changing the previous ones.

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

    Re: RANDBETWEEN-excel xp (sp-1)

    RANDBETWEEN is a volatile function which means it will change the result whenever another cell is changed.

    There are 2 possible solutions.
    1/ Once a random number is generated use copy & paste special to replace it with its value
    2/ Write your own function. I have taken and modified a function from Excel Tips to suit your needs.

    The function below used to include Application.Volatile but removing this stops the result from changing. Use the link above for an explanation of the function.

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


  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RANDBETWEEN-excel xp (sp-1)

    FYI, this won't change with a normal calculate. But if you do a full recalc (ctrl-alt-F9) it will calc a new random number.

    copy, pastespecial - values is the only way to stop it from changing.

    Steve

Posting Permissions

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