Positive random number generator from Normal distribution

S

susuru

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


Thanks.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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.
 
Upvote 0
<o:p> </o:p>
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:p> </o:p>
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:p> </o:p>
Anybody, please?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
wsjackman said:
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
Click to expand...

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?
 
Upvote 0
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%.

susuru said:
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?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
80
Mazurofgod
M
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
707
Peter_SSs
Peter_SSs
D
  • Solved
Need help with random number generator
Replies
3
Views
749
Fluff
Fluff
T
  • Solved
Random number generator button
Replies
3
Views
1K
Georgiboy
Georgiboy
J
  • Solved
Chance Existing Excel formula to provide positive or negative result
Replies
1
Views
445
iggydarsa
iggydarsa

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top