Compute a static random number (compute it once) in Excel

I think I have a much easier way to do this. Complete your spreadsheet, then apply the =RANDBETWEEN function for that column of numbers. Then do this:

1. Copy the results from that column.
2. Highlight the column and select "paste special" and select "values".

Now you have the values most recently created and they are static.

You can create a UDF (User-Defined Function):

Public Function Rand_once(ByVal r As Range)
    Rand_once = Rnd
End Function

In the cell where you want the result, you can put:

=Rand_once(A1)

The value will change (actually, being recalculated) only when the source value changes (aka A1).

You need a UDF with memory, so it knows if the cell has changed

This UDF will return a new random value when the refered to call changes, otherwise returns the last random value (ie no change)
Also return blank if source cell is blank (may or may not be what you require?)

Note: it has the problem that the Static values are lost when the sheet is closed, so the value will change each time the sheet is opened.

Function randonce(r As Range)
    Static trigger As Variant
    Static v As Double
    If r <> trigger Then
        v = Rnd
        trigger = r
    End If
    If Len(r) <> 0 Then
        randonce = v
    Else
        randonce = vbNullString
    End If
End Function

You can also use circular references to make a purely formula-driven "toggle switch," allowing the user to calculate a set of random numbers and then turn off further recalculations. Turn off circular reference warnings, and then put this formula in cell B3:

=IF($B$1="YES",RAND(),B3)

If cell B1 contains "YES", B3 will generate a new random number with each spreadsheet recalculation; if B1 contains any other value, the current value of B3 will be retained.