Positive random number generator from Normal distribution

[susuru]

Hi
<o> </o>
I would like to get random number generator for normal distribution and I’m using:
<o> </o>
=NORMINV(RAND(),$B$1,$B$2)
<o> </o>
Where B1 is mean (average) and B2 is SD.
<o> </o>
This command works, but in the random generating numbers, I’m getting positive and negative numbers (ex. -23,34).
<o> </o>
Please, how to change command so I can generate positive only numbers?


Thanks.

 

[Legacy 14611]

The formula
=NORMINV(0.025,0,1)
( mean of 0 and SD of 1) gives the value -1.96 (rounded to 2 dp).

This interprets as the value less than which only 2.5% of the area under the standard normal bell curve falls.

Equivalently, a random value proposed to be drawn from the standard normal has a .025 probability of being less than -1.96.

Are you looking for a different intepretation? Why do you have a problem with a negative value in this context?

You can always make the result appear positive by using the ABS function, that is

=ABS(NORMINV(0.025,0,1))

but I wouldn't recommend this unless you are entirely confident in what the resulting number actually means.

 

[susuru]

<o> </o>
Well, the thing is I need positive numbers generated from normal distribution, because it’s the matter of time data. It’s about in what time amount student coming to the end line.. Lets say in 45 s, than following student in 89 s , then in 15 s, ….. So I need positive numbers only, to play with them changing means and SD’s and to see various possibilities.
<o> </o>
Let’s say SD= 70 and MEAN = 60 , I need 45 numbers. Than I will need changing SD to 60 , 50 , 40 and so on …

<o> </o>
Anybody, please?

 

[wsjackman]

I am not sure the normal distribution is what you want,

I would normally recommend lognormal, gamma, maxwellian or some other distribution for times depending on what is needed.

but maybe

=max(0,NORMINV(RAND(),$B$1,$B$2))

will do what you want

 

[Legacy 14611]

What you seem to want is logically inconsistent with the normal distribution.

Whatever your mean and standard deviation, there always exists some probability say p%, where the cutoff value for the lower p% of the cumulative normal is negative.

Such probabilities may be very small, say if the mean is high and the SD is small, but they nonetheless exist. Since you use =RAND() as a probabilty generator in your example you will ultimately always generate some negative values for the NORMINV result.

You could consider using other distributions to avoid the sometimes negative result, such as the truncated normal, lognormal etc. Or you can ensure non-negativity by restricting the the admissible probabilities (to be say greater than 0.2, although the actual value will depend on your mean and SD).

I can't provide you with a valid solution, because one does not exist. Depending on the level of sophistication you are working at, you could however get an answer which is "good enough" to probably satisfy most purposes.

 

[susuru]

Thanks for your help guys.

Let's see

I have these numbers, which represents time in seconds

<table style="border-collapse: collapse; width: 73pt;" border="0" cellpadding="0" cellspacing="0" width="97"><col style="width: 73pt;" width="97"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 73pt;" align="right" width="97" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">71</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">79</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">96</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">160</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">118</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">122</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">6</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">208</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">109</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">105</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">89</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">17</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">89</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">18</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">24</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">15</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">97</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">121</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">46</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">43</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">170</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">132</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">87</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">34</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">21</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">270</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">46</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">88</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">16</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">108</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">197</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">52</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">61</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">27</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">36</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">16</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">229</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">203</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">7</td> </tr> </tbody></table>
with SD = 70 AND and mean 80 (round numbers)

Now I need random generated new 45 numbers which will be generated from normal distribution on changing of SD and means.

Let's say I need to get numbers (positive, becouse time can not be negative) with SD 10, 20 , 30 , 40 ..... and so.

Yes it's a bit going over normal distribution, but ....

Just crossed my mind: Can I get random 100 numbers (with SD I need) and to get just 45 positive from that line? Can that be done?

 

[susuru]

I am not sure the normal distribution is what you want,

I would normally recommend lognormal, gamma, maxwellian or some other distribution for times depending on what is needed.

but maybe

=max(0,NORMINV(RAND(),$B$1,$B$2))

will do what you want

It is giving positive numbers only , but I'm getting 6 or 7 zeros in 45 number roll. In that way SD is no longer the same. Can these zeros be numbers like others?

 

[tusharm]

Plot a frequency chart from the data and it does not look anything like normally distributed.

You may want to check for nomalcy using Mike Middleton's http://www.mikemiddleton.com/demand.pdf

You can estimate the probability of getting a negative number from a normal distribution. See the NORMDIST function. In your case, with mean=80 and std.dev=70, it is 12.7%.

Thanks for your help guys.

Let's see

I have these numbers, which represents time in seconds

<table style="border-collapse: collapse; width: 73pt;" border="0" cellpadding="0" cellspacing="0" width="97"><col style="width: 73pt;" width="97"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 73pt;" align="right" width="97" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">71</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">79</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">96</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">160</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">118</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">122</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">6</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">208</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">109</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">105</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">89</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">17</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">89</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">18</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">24</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">15</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">97</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">121</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">46</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">43</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">170</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">132</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">87</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">34</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">21</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">270</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">46</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">88</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">16</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">108</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">197</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">52</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">61</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">27</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">36</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">16</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">229</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">203</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">7</td> </tr> </tbody></table>
with SD = 70 AND and mean 80 (round numbers)

Now I need random generated new 45 numbers which will be generated from normal distribution on changing of SD and means.

Let's say I need to get numbers (positive, becouse time can not be negative) with SD 10, 20 , 30 , 40 ..... and so.

Yes it's a bit going over normal distribution, but ....

Just crossed my mind: Can I get random 100 numbers (with SD I need) and to get just 45 positive from that line? Can that be done?

 

[susuru]

Maybe exponential distribution will fit better.

 

[tusharm]

Maybe, and maybe something else.

How would you describe the graph when you plot the frequency distribution?

Maybe exponential distribution will fit better.