Excel, RAND()....how can one avoid repetition?

[Michael]

Hi everyone,

I am using RAND() but not much satisfied. I tried RAND()*RAND()
instead!

To my suprize, things are better. However, as you know, RAND()*RAND()
will exclude visiting some values (Imagine a 2-D chart where X varies
from 0 to 1, and so Y).

How can I have access to RAND()'s SEED?

Someone wrote me this:

" Excel uses the classic Monte Carlo sampling method for generating
the uniformly distributed random numbers. There is nothing wrong with
this except that Monte Carlo sampling does not span the full space of
interest unless you do a very large number of repetitions and you may
not know what large means prior to your run. This renders Monte Carlo
inefficient."

Have you heard of "Latin Hypercube" sampling approach?

Comment? Any better solution?

Thanks,
Mike

 

[Guest]

You might want ot explore this page:
http://www.mcgimpsey.com/excel/udfs/randint.html


tj

 

[Gord Dibben]

John McGimpsey has a UDF that will prevent repetition in Random numbers.

http://www.mcgimpsey.com/excel/udfs/randint.html

Gord Dibben Excel MVP

 

[Michael R Middleton]

Michael -

Hi everyone, I am using RAND() but not much satisfied. <

What are you using it for? Why are you not satisfied?

I tried RAND()*RAND() instead! To my suprize, things are better. <

In what way are things better?

However, as you know, RAND()*RAND() will exclude visiting some values
(Imagine a 2-D chart where X varies from 0 to 1, and so Y).<

The product will yield values between 0 and 1, but the product values are
not uniformly distributed. Values close to 0 are much more likely than
values close to 1.

How can I have access to RAND()'s SEED? <

You can't. If you use VBA, you can use RANDOMIZE with RND.

Someone wrote me this: " Excel uses the classic Monte Carlo sampling
method for generating the uniformly distributed random numbers. There is
nothing wrong with this except that Monte Carlo sampling does not span the
full space of interest unless you do a very large number of repetitions
and you may not know what large means prior to your run. This renders
Monte Carlo inefficient." Have you heard of "Latin Hypercube" sampling
approach? <

Latin hypercube sampling is implemented with the higher-end simulation
packages, e.g., Palisade's @Risk and Decisioneering's Crystal Ball.

Comment? Any better solution? <

It's not clear to me what your problem is, so it's difficult to propose a
solution. But you might want to visit www.palisade.com and
www.crystalball.com to see if they're helpful. And for a lower-cost
simulation package, you could try my RiskSim at www.treeplan.com.

- Mike

www.mikemiddleton.com

 

[Michael]

Michael -


What are you using it for? Why are you not satisfied?


In what way are things better?


The product will yield values between 0 and 1, but the product values are
not uniformly distributed. Values close to 0 are much more likely than
values close to 1.


You can't. If you use VBA, you can use RANDOMIZE with RND.


Latin hypercube sampling is implemented with the higher-end simulation
packages, e.g., Palisade's @Risk and Decisioneering's Crystal Ball.


It's not clear to me what your problem is, so it's difficult to propose a
solution. But you might want to visit www.palisade.com and
www.crystalball.com to see if they're helpful. And for a lower-cost
simulation package, you could try my RiskSim at www.treeplan.com.

- Mike

www.mikemiddleton.com

Mike,

I have an optimization model and using excel optimizer. One of the
constraints's bound has to vary within a range everytime the worksheet
is visited.

When using RAND(), I get at most 2 different solutions. When I
switched to RAND()*RAND(), I get about 8 different solutions!

I know that RAND()*RAND() values are closer to ZERO since some values
between 0 and 1 wont be visited. But, the issue is to get more
different solutions and yes RAND()*RAND() is doing better job than
RAND()!

Comment?

Now, I don't know if Latin Hypercube will do any good!? How it works?
Assume that have 10 constraints and want its bounds to vary within a
pre-defined range, would LH still be applicable here? I used
RAND()*RAND() for each of the 10 constraints and getting 5 times more
different solutions than if use RAND() alone for each of the 10
constraints!

Comments?

Thanks alot,
Mike (not you)

 

[Michael R Middleton]

Michael -

I have an optimization model and using excel optimizer. One of the
constraints's bound has to vary within a range everytime the worksheet is
visited. When using RAND(), I get at most 2 different solutions. When I
switched to RAND()*RAND(), I get about 8 different solutions! I know that
RAND()*RAND() values are closer to ZERO since some values between 0 and 1
wont be visited. But, the issue is to get more different solutions and yes
RAND()*RAND() is doing better job than RAND()! Comment? <

(1) Solver recalculates the worksheet at each iteration. So I would expect
the values of RAND() to change with each iteration, implying that Solver is
trying to solve a model with different constraint values at each iteration.
I think you would want the constraint values to stay the same for a complete
set of Solver iterations.

(2) I don't understand why the issue is "to get more different solutions."

Now, I don't know if Latin Hypercube will do any good!? How it works?
Assume that have 10 constraints and want its bounds to vary within a
pre-defined range, would LH still be applicable here? I used RAND()*RAND()
for each of the 10 constraints and getting 5 times more different
solutions than if use RAND() alone for each of the 10 constraints!
Comments? <

(3) You can learn about LH by doing a Google search, or go to
www.crystalball.com and search their site for "hypercube." Simply stated,
for a given number of trials in a simulation, LH will systematically span
the range of possible outcome values, while Monte Carlo will produce random
values that might be clumped together.

(4) Whether you use standard Monte Carlo simulation or LH to generate random
numbers, the important point (see (1) above) is that set of
randomly-selected constraint values should stay constant while Solver is
finding an optimal solution. And you cannot ensure that using only worksheet
functions. You need VBA.

(5) Your problem may be amenable to using Crystal Ball's OptQuest or
Palisade's RiskOptimizer. Or, more likely (I can't tell because you haven't
fully described the purpose of your analysis) you may only need VBA to
handle Solver for each set of randomly-selected constraint values. Both
approaches are discussed in Powell and Baker, "The Art of Modeling with
Spreadsheets." Also, there are several books by Wayne Winston that may be
helpful.

- Mike

www.mikemiddleton.com

 

[medicenpringles]

speaking of RAND()....

is there a way to re-define the range RAND() gets its numbers from?
would like to have it chose numbers between 0 and 10. can you do this

 

[Max]

speaking of RAND()....

is there a way to re-define the range RAND() gets its numbers from? i
would like to have it chose numbers between 0 and 10. can you do this?

Try: =RAND()*10

 

[medicenpringles]

well not exacly, but *100 worked great! thanks

 

[Max]

well not exacly, but *100 worked great! thanks.

... well, as long you're happy ..
(guess your specs might have changed a little <g>)

 

[Michael]

Michael -


(1) Solver recalculates the worksheet at each iteration. So I would expect
the values of RAND() to change with each iteration, implying that Solver is
trying to solve a model with different constraint values at each iteration.
I think you would want the constraint values to stay the same for a complete
set of Solver iterations.

Mike, I am actually NOT using Excel optimizer but MPL/CPLEX. So,
problem (1) doesn't occure here. Yes, it would happen if using Excel.

(2) I don't understand why the issue is "to get more different solutions."

My application is a multi-criteria one so wants Pareto set of
solutions not just one.

(5) Your problem may be amenable to using Crystal Ball's OptQuest or
Palisade's RiskOptimizer. Or, more likely (I can't tell because you haven't
fully described the purpose of your analysis) you may only need VBA to
handle Solver for each set of randomly-selected constraint values. Both
approaches are discussed in Powell and Baker, "The Art of Modeling with
Spreadsheets." Also, there are several books by Wayne Winston that may be
helpful.

Will look into that book. Regarding LH, I am just trying to know how
it works but not necessarily will use it.

Regards,
Mike