Random Number function

[Ed Schillmoeller]

I want to use the RAND and RANDBETWEEN functions but don't want the numbers
to change every time a calculation occurs. Can this be done.

I'm using Excel included in Office:mac v.X

 

[Bob Greenblatt]

I want to use the RAND and RANDBETWEEN functions but don't want the numbers
to change every time a calculation occurs. Can this be done.

I'm using Excel included in Office:mac v.X

Once the number is in the cell, simply copy the cell(s) and paste special
values. This will remove the formulas and replace with the value so it will
no longer recalculate.

 

[Bernard Rey]

In addition to Bob's reply, if you want to have stand still, but keep a
possibility to change it the next time you'll use it or whaterver, you might
like to keep the formulas in your sheet. In that case, you can use a
function, based on the RND function in VBA.

In the VBEditor, insert a module and paste this:

Function NewRand()
NewRand = Rnd
End Function

Then in your sheet, paste "=newrand()" (without the quotes) wherever you
need, and as many times as you need, just like the standard RAND function.
It will only run once in each cell and then stay as is, but you can run it
again if wanted, when editing the formula in the cell.

You can even keep a hand on the way it runs, using the "Volatile" property
in VBA. This time, the Function to paste in the VBA module could be:

Function AnotherRand(ByVal i As Integer)
If i > 0 Then
Application.Volatile True
Else
Application.Volatile False
End If
AnotherRand = Rnd
End Function

In your sheet, paste "=anotherrand(A1)" this time. Now if the value in cell
A1 is 0 or less, the result won't change. But if the value in cell A1 is
greater than 0, the value will be renewed each time (just like the RAND
Worksheet function).