Random Generator in Excel?

[Rebekka-Marie]

Hello everybody,

I would like to distribute Demand randomly over 30 days.

(Possibly with a specific Demand sum overall and maybe a stated variance in Demand changeing)

Is there a Random Generator in Excel which could help me to do that?

Rebekka

 

[joeu2004]

I would like to distribute Demand randomly over 30 days.
(Possibly with a specific Demand sum overall and maybe a
stated variance in Demand changeing)
Is there a Random Generator in Excel which could help me
to do that?

Perhaps. Your description of the random distribution and stochastic process
is not clear to me. But you might take a look at the RNGs provided with the
Data Analysis tools.

Alternatively, you can craft your own RNG using RND and RANDBETWEEN
functions. Again, your RNG requirements are not clear enough for me to be
specific.

The downside is of using RND and RANDBETWEEN: they are "volatile"
functions. They change every time any cell in any worksheet in the workbook
is modified directly (edited). There are methods to avoid that or at least
mitigate the impact.

 

[Auric__]

The downside is of using RND and RANDBETWEEN: they are "volatile"
functions. They change every time any cell in any worksheet in the
workbook is modified directly (edited). There are methods to avoid that
or at least mitigate the impact.

Can always use VBA to avoid the volatile functions. Just sayin'.

 

[Rebekka-Marie]

Am Montag, 20. August 2012 19:08:54 UTC+2 schrieb joeu2004:

Perhaps. Your description of the random distribution and stochastic process

is not clear to me. But you might take a look at the RNGs provided with the

Data Analysis tools.



Alternatively, you can craft your own RNG using RND and RANDBETWEEN

functions. Again, your RNG requirements are not clear enough for me to be

specific.



The downside is of using RND and RANDBETWEEN: they are "volatile"

functions. They change every time any cell in any worksheet in the workbook

is modified directly (edited). There are methods to avoid that or at least

mitigate the impact.

This is exactly what I need. Could you explain to me in further detail what to do for my case:

Total demand for 1 month: 150 units
Covariance: 0,5

-> Distribute 80 units randomly over 30 cells.

 

[joeu2004]

Am Montag, 20. August 2012 19:08:54 UTC+2 schrieb joeu2004:

you might take a look at the RNGs provided with the
Data Analysis tools.
Alternatively, you can craft your own RNG using RND
and RANDBETWEEN functions.

[....]
This is exactly what I need. Could you explain to me
in further detail what to do for my case:
Total demand for 1 month: 150 units
Covariance: 0,5
-> Distribute 80 units randomly over 30 cells.

I can probably help. But your terminology is unclear to me.

First, it is unclear which of the two alternative are "exactly what you
need". Did you look at the RNGs in the Data Analysis tools?

-----

Second, do you really mean "variance", not "covariance"? And do you really
mean "standard deviation", not "variance"?

"Covariance" is a measure of the relationship between two random variables.
That does not seem to be the case here. See the Excel Help page for COVAR.

It is not uncommon for people to misuse the term "variance" (var) when they
really mean "standard deviation" (sd). Technically, var is the square root
of sd. See the Excel Help pages for VARP and STDEVP.

Alternatively, perhaps you do not mean any of those statistical terms. It
is not uncommon for accountants, for example, to talk about "variance" when
they simply mean "variation" (difference).

-----

Third, I am confused by your two facts: 1 month demand of 150 units, versus
distributing 80 units over 30 cells.

I do not understand what a cell represents in terms of time: day; month;
something else?

-----

Finally, it is not sufficient to specify total units (80 or 150?),
variance(var = 0.5?), and number of samples (30 cells).

You also need to specify the expected distribution, for example: normal
(bell-shaped), uniform (probably not), poisson, etc?

For a normal distribution, we need to know an average per cell (month?) as
well as the variance or standard deviation.