Re: Positive random number generator from Normal distribution
susuru:
There are expensive statistical software packages available.
In many cases Excel will handle the job.
The solution may depend on what you are trying to accomplish and why.
As wsjackman noted the normal distribution may not be best for simulated time data.
The normal distribution can be used for simulations of data.
The normal distribution can be used if both side of the plot are cut off.
In Column B is a simulated PDF (Probability Distribution Function) for the Normal Distribution, with the tailing ends of the curve cut off on both sides, normalized to 1.
Note this is not a series of integers.
IN A2: =SUM(A4:A23)
IN B2: =SUM(B4:B23) {Normalized distribution = 1}
IN A4 (copied down):
=NORMDIST(ROW()-3,D$1,B$1,0)
IN B4 (copied down):
=A4/A$2
IN A25: =STDEV(A4:A23)
IN A26: =STDEVP(A4:A23)
The sum of the PDF is used in VLOOKUP:
D4 = 0
IN D5 (copied down):
=SUM(B$4:B4)
For integers 1 to 20 (instead of 300), std = 4 (Cell B1), Center = 10.5 (Cell D1)
Next, the normalized PDF can be used to generate a series of integers.
For the data below note that column B can be plotted on the x axis and Column C on the y axis.
Below is the histogram showing that in Column B there were 3 'Integer 8' and 0 'Integer 9' and 2 'Integer 10'.
Start with 20 integers that can be randomly selected according to the PDF from 1 to 20.
Starting in Cell B31, select B31 to B50 and enter, with CTRL+SHIFT+ENTER:
=FREQUENCY(C31:C50,A31:A50)
This will appear in Cells B31 to B50 as:
{=FREQUENCY(C31:C50,A31:A50)}
In Cell C31 (copied down to C50):
=VLOOKUP(RAND(),D$4:E$23,2)
Code:
A B C
30 3.393492224
31 1 0 13
32 2 0 15
33 3 0 14
34 4 1 13
35 5 0 10
36 6 1 12
37 7 0 12
38 8 3 18
39 9 0 15
40 10 2 8
41 11 2 11
42 12 3 8
43 13 2 6
44 14 3 4
45 15 2 12
46 16 0 11
47 17 0 10
48 18 1 14
49 19 0 14
50 20 0 8
Sheet1
[Table-It] version 09 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B31:B50 {=FREQUENCY(C31:C50,A31:A50)}°
C30 =STDEV(C31:C50)
C31:C50 =VLOOKUP(RAND(),D$4:E$23,2)
{=formula}°:
select entire range
enter formula without {}
confirm with Control-Shift-Enter
[Table-It] version 09 by Erik Van Geit
Note that the plot of the integers will more closely resemble the plot of the PDF as the number of integers increases.
For 30 integers that can be randomly selected according to the PDF from 1 to 20:
In Cell B31, edit and CTRL+SHIFT+ENTER:
=FREQUENCY(C31:C60,A31:A50)
This will appear in Cells B31 to B50 as:
{=FREQUENCY(C31:C60,A31:A50)}
In Cell C30
=STDEV(C31:C60)
=3.750249
In Cell C31 (copied down to C60):
=VLOOKUP(RAND(),D$4:E$23,2)
Code:
A B C
31 1 0 11
32 2 0 9
33 3 1 8
34 4 1 7
35 5 2 14
36 6 1 10
37 7 3 11
38 8 3 5
39 9 3 12
40 10 2 3
41 11 6 11
42 12 1 4
43 13 2 11
44 14 2 15
45 15 1 7
46 16 0 19
47 17 1 7
48 18 0 5
49 19 1 6
50 20 0 10
51 14
52 9
53 17
54 11
55 13
56 11
57 8
58 13
59 9
60 8
Sheet1
[Table-It] version 09 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B31:B50 {=FREQUENCY(C31:C60,A31:A50)}°
C31:C60 =VLOOKUP(RAND(),D$4:E$23,2)
{=formula}°:
select entire range
enter formula without {}
confirm with Control-Shift-Enter
[Table-It] version 09 by Erik Van Geit
Note that the series of integers will not correspond exactly to the PDF used to generate them.
The resulting average and STD will vary from the target.
The input STD can be varied to adjust the output STD.
Note that for INPUT STD = 4 THE OUTPUT STDEV OR STDEVP TENDS TO BE CLOSE TO 4.
This is because not much of the tailing curve is cut off on both sides of the plot.
For higher input, for example STD = 10, output STDEV = 5.427 for one simulated run with 200 integers selected from 1 to 20.
For STD = 10 much of the INPUT PDF is cut off on both sides of the plot.
Note that RAND() function recalculates with every cell change.
Note that for very large input STD the chance of selecting every integer is almost exactly the same.
> </o
