How to 'freeze' the output generated by RAND()?

[hello_lpc]

Hi all,
This is my first post in this forum and I wonder if anyone could help me...

I have a column(a1:A11) of numbers generated by RAND(). But it is really
annoying that it will re-calculate the outputs everytime!
Is it possible to use RAND() to randomise for one time only??

THANKS!

George Lin

[Dav]

The simple solution is to select the cells and post special values back on top of the cells. This will leave them fixed for ever as they will now just be values

Regards

Dav

[Bob Phillips]

Two ways.

Either copy the random values and then Edit>Pastespecial and check Values,

or

First, goto Tools>Options>Calculation and check the Iteration box to stop
the circular references message, and then in A1, use

=IF($B$1<>"",A1,RAND())

copy this down as far as you want. This will recalculate until you enter a
value in B1, it will then lock.

With the second method, any real circular references messages will be
suppressed.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"hello_lpc" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all,
> This is my first post in this forum and I wonder if anyone could help
> me...
>
> I have a column(a1:A11) of numbers generated by RAND(). But it is
> really
> annoying that it will re-calculate the outputs everytime!
> Is it possible to use RAND() to randomise for one time only??
>
> THANKS!
>
> George Lin
>
>
> --
> hello_lpc
> ------------------------------------------------------------------------
> hello_lpc's Profile:
http://www.excelforum.com/member.php...o&userid=31304
> View this thread: http://www.excelforum.com/showthread...hreadid=509935
>

[hello_lpc]

The simple solution is to select the cells and post special values back on top of the cells. This will leave them fixed for ever as they will now just be values

Regards

Dav

Thanks Dav, that really helps!

George

[hello_lpc]

Two ways.

Either copy the random values and then Edit>Pastespecial and check Values,

or

First, goto Tools>Options>Calculation and check the Iteration box to stop
the circular references message, and then in A1, use

=IF($B$1<>"",A1,RAND())

copy this down as far as you want. This will recalculate until you enter a
value in B1, it will then lock.

With the second method, any real circular references messages will be
suppressed.

--

HTH

Bob Phillips

Thanks for your help,Bob

[[email protected]]

"hello_lpc" wrote:
> I have a column(a1:A11) of numbers generated by RAND().
> But it is really annoying that it will re-calculate the outputs
> everytime!

..... Every time there is a change __anywhere__ in the
spreadsheet! Annoying, to say the least. It makes RAND()
almost useless, IMHO.

> Is it possible to use RAND() to randomise for one time only??

I replace all uses of RAND() with myrand() after creating the
following macro:

1. Type alt-F11 to enter the VB editor.
2. Cick Insert > Module.
3. Enter the following, then close the VBE window:

function myrand(optional rng)
myrand = rnd()
end function

You can use this in either of the following ways: myrand(),
myrand(A1), or myrand(A1:C1).

Thus, random values are recalculated only when the entire
spreadsheet (or the cell or range passed to "rng") is recalculated.