Hi,
How to create a column of pseudo random numbers with probability '
conditioned ' without resources or programming macros in excel:
understand conditional probability by one that for example for the
number 1 is 3% to the number 2 is 9% for number 3 is 2% etc.
Starting from the principle that this is how can I solve this
mathematical problem in excel by creating a column of random numbers
according to this conditional probability without resorting to macros
or VB programming?
Thanks for the support, thank you!
Basically you need to do the following:
1) Create a table of your probability distribution. That's the
probabilities
you have given here, but for all possible values. Depending on exactly
what you mean by "for the number 1" that might just be a discrete
table. So maybe it's just for the integers 1 through 6? Or 1 through
10?
If you mean it's for the range 0 to 1, then you need to make the table
be the values that apply to ranges.
2) Create a table of your cummulative distribution function (CDF).
This is
just the integral of the previous table.
So if you've got .03 for 1, .09 for 2, .02 for 3, etc., then you
get a cummulative distribution function of
0 of for no number at all
..03 for 1,
..03 + .09 = .12 for 1 or 2
..12 + .02 = .14 for 1, 2, or 3
And so on up to 1 for all numbers in the range
The CDF goes from 0 to 1 over the range of your possible values.
3) Now you need to build the inverse of your CDF. The CDF gives
you the probability of a given value or less. You need a table
that will give you the value that has a given probability.
So in the example, for probability 0 to 0.03, the inverse gives 1.
For 0.03 to .12, the inverse gives 2.
For 0.12 to 0.14, the inverse gives 3.
Make a table that lets you look up the correct number given
the probability.
4) Once you've got that in a table, you can use a lookup to
generate your properly conditioned random numbers.
Generate a random number between 0 and 1.
Look it up in the table from step 3).
That gives you your random number. And it will
have the distribution specified.
Your example gave your numbers as discrete integers.
If that's all you want, you don't need to read farther.
Suppose you wanted something that worked for floats.
Say you wanted something where you generted a
random from 0 to 1, with probability proportional to
the value. That is,
prob(x) = C x, 0 <= x <= 1
The total probability has to be 1, so the integral of
this from 0 to 1 has to be 1. So C has to be 2.
prob(x) = 2 x
The CDF for this is just
CDF(x) = integral from 0 to x (p(y) dy) = x^2
And the inverse of CDF(x) gives invCDF = sqrt(x)
So, if you generate random numbers in the range
0 to 1, then take their square root, they will be
distributed according to prob(x) = 2x.
