I have posted this question in the wrong place, so here I am again.

I want to generate a random number using RAND() in a cell.

But the number generated by RAND() will change every time there is a
recalculation.

Once the number is generated, is there a way to make it NOT to change under
any situation?

Thanks in advance

Charles.

In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1
to overcome the circular reference problem.
--
David Biddulph

"Charles W" wrote in message
...
I have posted this question in the wrong place, so here I am again.

I want to generate a random number using RAND() in a cell.

But the number generated by RAND() will change every time there is a
recalculation.

Once the number is generated, is there a way to make it NOT to change
under
any situation?

Thanks in advance

Charles.

Thanks for the help. How do you "set the calculation iteration count to 1"?

Actually, I tried just using the formula you suggested without "setting the
calculation iteration count to 1" (because I don't know how), and all I got
was "0"

Charles.

"David Biddulph" wrote:

In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1
to overcome the circular reference problem.
--
David Biddulph

"Charles W" wrote in message
...
I have posted this question in the wrong place, so here I am again.

I want to generate a random number using RAND() in a cell.

But the number generated by RAND() will change every time there is a
recalculation.

Once the number is generated, is there a way to make it NOT to change
under
any situation?

Thanks in advance

Charles.

I found out how to set the count now.

The formula works like a charm.

One more problem, when I copy the formula in A1 to A2 by dragging, the same
Random number is generated. How can I make it to generate a new number in
another cell when I copy? I am lazy and don't want to type the formula in a
new cell every time.

Thanks again

Charles.

"David Biddulph" wrote:

In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1
to overcome the circular reference problem.
--
David Biddulph

"Charles W" wrote in message
...
I have posted this question in the wrong place, so here I am again.

I want to generate a random number using RAND() in a cell.

But the number generated by RAND() will change every time there is a
recalculation.

Once the number is generated, is there a way to make it NOT to change
under
any situation?

Thanks in advance

Charles.

Hi,

Press F9.

However, if you want to generate a lot of random numbers with RAND and you
want them static. Enter the formula, copy it as needed and then choose Copy,
and Edit, Paste Special, Values. This will convert all the formulas to
values.

If this approach works for you I would return the iteration to its default,
it could cause problems elsewhere.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Charles W" wrote:

I found out how to set the count now.

The formula works like a charm.

One more problem, when I copy the formula in A1 to A2 by dragging, the same
Random number is generated. How can I make it to generate a new number in
another cell when I copy? I am lazy and don't want to type the formula in a
new cell every time.

Thanks again

Charles.

"David Biddulph" wrote:

In A1 use =IF(A1=0,RAND(),A1) and set the calculation iteration count to 1
to overcome the circular reference problem.
--
David Biddulph

"Charles W" wrote in message
...
I have posted this question in the wrong place, so here I am again.

I want to generate a random number using RAND() in a cell.

But the number generated by RAND() will change every time there is a
recalculation.

Once the number is generated, is there a way to make it NOT to change
under
any situation?

Thanks in advance

Charles.

On Feb 23, 11:40*am, Charles W
wrote:
But the number generated by RAND() will change
every time there is a recalculation.

Yeah, real useful, huh? :-(


Once the number is generated, is there a way to
make it NOT to change under any situation?

Copy-and-paste-special-value. You can overwrite the RAND() formulas,
or paste-special the values somewhere else and simply ignore the cells
with RAND(). An advantage of the latter is that you can "regenerate"
random values again later simply by using copy-and-paste-special-value
again.

Alteratively, create the following macro.

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

The argument provides the option of causing the MYRAND formulas to be
recalculated automagically simply by modifying a cell in the range
argument. For example, =MYRAND(A1) is recalculated whenever A1 is
modified. (There are other ways to accomplish the same thing.)

If you are not familiar with macros, press alt-F11, click on
Insert - Module, and copy-and-paste the above function into the VBE
window. Be sure to set macro security to Medium or higher in order to
make life a little easier when you reopen the workbook.

A couple downsides to the macro approach.

First, it makes it more difficult to share your workbook with other
people, since it depends on their macro security.

Second, the VBA Rnd function might not be as robust as the Excel RAND
function. I don't know that for a fact. But the VBA Rnd function
returns a 32-bit floating-point value, whereas the Excel VBA RAND
function presumably returns a 64-bit floating-point value.

On the other hand, what really determines robustness of an RNG is the
internal algorithm, which might not be reflected in the function data
type. A 64-bit result does not necessarily have a longer period or
better random characteristics. Perhaps some other people can comment,
if they have knowledge of the internal algorithms of Rnd and RAND.

PS....

On Feb 23, 3:39*pm, I wrote:
A couple downsides to the macro approach.

Another downside: the MYRAND expressions are normally recalculated
when you save the workbook, unless you select Manual and unselect
"Recalculate before save" under Tools Options Calculation. Kinda
defeats the benefit of using a macro to generate the random value.

Still, I use the macro approach sometimes in quick-and-dirty
worksheets, which I don't save or don't care if the values are not
preserved across save and re-open.

Charles!

Try out my excel addin function randStatic at :

www.pimpmyexcel.com

Look under statistical functions

Custom Excel Function: randStatic()

Category
Statistical Functions

Arguments
None

Returns
A mersenne twister generated random number in [0,1]
The function is non volatile, ie it only will not change on
recalculation

Examples
randStatic() returns a [0,1] random number only once

Hello,

...
Try out my excel addin function randStatic at :

www.pimpmyexcel.com

...

No open source?

No license?

No rights at all?

Sorry, no use!

Regards,
Bernd

pimpmyexcel

And you want to be taken seriously?

--
Biff
Microsoft Excel MVP


wrote in message
...
Charles!

Try out my excel addin function randStatic at :

www.pimpmyexcel.com

Look under statistical functions

Custom Excel Function: randStatic()

Category
Statistical Functions

Arguments
None

Returns
A mersenne twister generated random number in [0,1]
The function is non volatile, ie it only will not change on
recalculation

Examples
randStatic() returns a [0,1] random number only once