Random Numbers

[Soccer boy]

i am trying to run a precipitation model and am using the Rand() function to
help generate numbers inbetween certain values. I am also using solver in my
spreadsheets, The solver is having a hard time calculating everything
because the random numbers are being regenerated every time. Without Copy
and Special Pasteing the Value of the generated random number, is there
another way i can keep the Rand() function from generating new numbers
everytime and only when i tell it too.

 

[Sheeloo]

Short answer is 'No'.

Whenever the worksheet gets recalculated, RAND() will generate a new number.

 

[Rick Rothstein]

No, not with RAND. Can you use a VB solution... that can be made to generate
random numbers without their constantly changing. If a VB solution is
possible for you, then describe how they should work. Do you need upper and
lower bounds, or is 0 to just under 1 good enough? Can there be repeats or
not? Any other restrictions?

 

[joeu2004]

Without Copy and Special Pasteing the Value of the
generated random number, is there another way i can
keep the Rand() function from generating new numbers
everytime and only when i tell it too.

Not directly. But if you are amenable to using a VBA function, you
can bury the random number generation by using the VBA Rnd()
function. For example:

function myrand(optional rng as range) as double
myrand = rnd()
end function

With the optional range parameter, you can call it with simply MyRand
() or MyRand(A1). With the latter form, MyRand will be called
whenever A1 is modified (and whenever the worksheet is recalculated as
a whole).


----- original posting -----

 

[pomegranate-man]

i am trying to run a precipitation model and am using the Rand()

function to help generate numbers inbetween certain values. I am also
using solver in my spreadsheets, The solver is having a hard time
calculating everything because the random numbers are being
regenerated every time. Without Copy and Special Pasteing the Value
of the generated random number, is there another way i can keep the
Rand() function from generating new numbers everytime and only when i
tell it too.

One way is to first allow circular references by checking the box:
Tools > Options > Calculation > Iteration

Then in B1 use a formula like:
=IF($A$1="",RAND(),B1)

As long as A1 is non-empty, B1 won't change.

Caution: allowing circular references can be a risk. Later, if you make a
circular reference in the workbook by mistake, Excel won't flag it as an
error.