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

Two ways.

Either copy the random values and then EditPastespecial and check Values,

or

First, goto ToolsOptionsCalculation 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" wrote
in message ...

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

Bob Phillips Wrote:
Two ways.

Either copy the random values and then EditPastespecial and check
Values,

or

First, goto ToolsOptionsCalculation 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
------------------------------------------------------------------------
hello_lpc's Profile: http://www.excelforum.com/member.php...o&userid=31304
View this thread: http://www.excelforum.com/showthread...hreadid=509935

Bob Phillips Wrote:
Two ways.

Either copy the random values and then EditPastespecial and check
Values,

or

First, goto ToolsOptionsCalculation 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
------------------------------------------------------------------------
hello_lpc's Profile: http://www.excelforum.com/member.php...o&userid=31304
View this thread: http://www.excelforum.com/showthread...hreadid=509935

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=509935

Dav Wrote:
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
------------------------------------------------------------------------
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" 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.