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]

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

QUOTE]

Thanks for your help,Bob

 

[hello_lpc]

Two ways.

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

or

First, goto Tools>Options>Calculation and check the Iteration box t
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 yo
enter a
value in B1, it will then lock.

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

--

HTH

Bob Phillips

QUOTE]

Thanks for your help,Bob :

 

[joeu2004]

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.