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