random number from normal dist

  • Thread starter Thread starter T
  • Start date Start date
T

T

I'd like to generate a series of random numbers in a
column from a normal distribution defined by means and
standard deviations defined in two separate columns. Any
excel functions that might do this?

I know the random number generator does this, but I want
to iterate ~ 44000 times, with different means and stdevs
each time.

thanks

Tarun
 
Perhaps something along these lines ..

First, since we're going to use data tables
to generate the random number iterations (calculation intensive),
it's better to switch the calculation mode
to either "Manual" or "Automatic except tables" first
(via Tools > Options > Calculation tab
Check "Manual" or "Automatic except tables" > OK)
Click to expand...

In Sheet1
-----------
Assume the means and standard deviations are
in cols A and B, data from row2 down

Let's assume you have 5 sets of
means and standard deviations in A2:B6

Put in C2: =NORMINV(RAND(),A2,B2)
Copy C2 down to C6

This will generate one random number in col C
per set of mean and standard deviation in cols A and B

Iterations set-up
------------------
In a new Sheet2
------------------
Say we want to generate a sample set of 10 random numbers
for each formula in Sheet1 C2:C6

Put in A2: =ROW()-1
Copy down to A11
(This will put sequential numbers 1 to 10 in A2:A11)

Put in B1: =OFFSET(Sheet1!$C$1,COLUMN()-1,,)
Copy B1 across to F1

The above is equivalent to putting
in B1: =Sheet1!C2
in C1: =Sheet1!C3
in D1: =Sheet1!C4
and so on

The OFFSET formula enables a much easier alternative
- just copy right from B1 - instead of having to link the cells one by one

Now for the data table set-up
-------
Select A1:F11

Click Data > Table

Leave the "Row input cell:" box empty

For "Column input cell:" box, input: A1
(or just click inside the box, then click on A1)

Note: "A1" is arbitrary, any empty cell outside
of A1:F11 can also be used for the column input

Click OK

In B2:F11 will be the 10 random numbers per column
iterated for each formula in Sheet1's C2:C6, viz.:

B2:B11 are the iterations for the formula in C2 of Sheet1
with those in C2:C11 for the formula in C3 of Sheet1
and so on, generated in the column-wise manner desired

Each press of F9 key will generate a fresh set of random numbers

To freeze the random numbers, just select
and copy and paste special as "values" elsewhere
--

Extend the constructs above to suit
the number of sets of means and standard deviations in Sheet1
and the desired number of iterations you want in Sheet2

The set-up will enable you to generate all the 44,000? iterations
per set of mean and standard deviation at one go in Sheet2

(Albeit you'll probably have to wait a couple of minutes
for the intense number crunching to complete <g>)
 
Back
Top