Random Generator in Excel?

  • Thread starter Thread starter Rebekka-Marie
  • Start date Start date
R

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
 
Rebekka-Marie said:
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?
Click to expand...

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.
 
joeu2004 said:
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.
Click to expand...

Can always use VBA to avoid the volatile functions. Just sayin'.
 
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.
Click to expand...

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.
 
Rebekka-Marie said:
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.
Click to expand...
[....]
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.
Click to expand...

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.
 
Back
Top