Random Number generator

[Neil M]

When I use random number generator, is there a way to make specific random
numbers remain the same based on data in other cells?

 

[joeu2004]

When I use random number generator, is there a way to
make specific random numbers remain the same based
on data in other cells?

I create my own RNG function in a macro, often simply calling
the VBA Rnd() function. Excel then does the "right" thing, which
is to call the VBA function only when the entire worksheet is
recalculated or when a referenced cell is recalculated.

I might define myrand() as follows:

function myrand(optional range)
myrand = Rnd()
end function

Then I can use it in the worksheet in any of the following ways:

=myrnd()
=myrnd(A1)
=myrnd(A1:C10)

The first form will call myrnd() only one time and whenever
the entire worksheet is recalculated.

The second form will also call myrnd() when A1 is recalculated.

The third form will also call myrnd() when any cell in the range
is recalculated.

Caveat: Normally the entire worksheet is recalculated only
when you type shift-ctrl-F9. However, I discovered that the
entire worksheet is also recalculated whenever you delete
another worksheet(!). The only way I know to avoid that is
to disable auto-recalc. Of course, sigh, that defeats the
purpose of avoiding recalc by hiding the RNG inside a VBA
function in the first place -- unless you like recalculating
individual cells ;-).

PS: You can create the above VBA function by typing alt-F11
and clicking Insert > Module in the VBE. Unless you want to
sign your macros (a good idea, I guess), you might also need
to weaken your macro security by clicking Tools > Macros >
Security > Security Level > Medium in Excel (not VBE). This
prompts you to enable macros when you open workbooks
that have macros.

 

[joeu2004]

Errata ....

Then I can use it in the worksheet in any of the following ways:
=myrnd()
=myrnd(A1)
=myrnd(A1:C10)

Of course, all instances of "myrnd" shoud be "myrand". I
variously use "rnd" and "rand" in the name because of the
different in spelling between Excel and VBA. "Sometimes
you feel like a nut; sometimes you don't" .