Random Number generator

[Kosman]

Hi,

I want to make 50 tables (matrices) with 150 numbers each. The numbers should be randomized either a 0 or 1.

I first thought i would create a 10 by 15 matrix:
- Generate a random number between 1 and 150. (for the amount of 1s in the table)
- Generate a random number between 1 and 10. (for the collumn)
- Generate a random number between 1 and 15. (for the row)

And then keep refreshing column and row numbers and enter the amount of 1s in the matrix.

As i do not want to make the 50 tables by hand. Does anyone know how to generate such tables?

Thanks in advance!

 

[alansidman]

I'm a bit confused

[COLOR=#333333]- Generate a random number between 1 and 150. (for the amount of 1s in the table)[/COLOR]

Are you looking for the numbers between 1 and 150 or are you looking to place only 1 and 0 in these 150 cells. Your explanation confuses me.

 

[shg]

Why not just enter the formula =randbetween(0,1) in all the cells?

 

[Kosman]

I'm a bit confused

[COLOR=#333333]- Generate a random number between 1 and 150. (for the amount of 1s in the table)[/COLOR]

Are you looking for the numbers between 1 and 150 or are you looking to place only 1 and 0 in these 150 cells. Your explanation confuses me.

sry,

I am looking to place only 1 an 0 in the 150 cells.

 

[joeu2004]

My interpretation.... Kosman wants to generate 50 10x15 matrices of random 0s and 1s such that the sum of each matrix will be between 1 and 150 randomly.

It is interesting that Kosman said between 1 and 150, not 0 and 150. So technically, the solution should guarantee at least one 1, randomly placed.

But practically, it is a "good bet" that there will be at least one 1, even if we don't make an effort to "guarantee" it. Is the probability of at least one 1: 1 - BINOMDIST(1,150,0.5,1) ?

 

[Kosman]

Why not just enter the formula =randbetween(0,1) in all the cells?

Because that will give me tables which all have approx 75 0s, and 75 1s.

I will not get anything like 30 1s and 120 0s, or vice versa.

The output is then not normally distributed.

 

[Kosman]

Joeu2004... First sentence is on point!

Do you mean to use BINOMDIST(1,150,0.5,1) 150 times? (I get lot of different values).

 

[shg]

Because that will give me tables which all have approx 75 0s, and 75 1s.

I will not get anything like 30 1s and 120 0s, or vice versa.

The output is then not normally distributed.

The sums of 150 random binary numbers will assuredly have a normal distribution (or a binomial distribution so close to normal that only God can tell the difference) with a mean of 150 * 0.5 = 75 and a SD of 150 * 0.5 * (1 - 0.5) ~ 6.124.

But if all you're looking for is a bunch of normally-distributed numbers with a certain mean and SD, use =NORM.INV(RAND(), mean, SD).

If you always want integers, round the result. So, for example, you could use =NORM.INV(RAND(), 75, 20).

That could (eventually will) include negative results. If that's not allowable, then you want log-normal random variates.

 

[shg]

... then you want log-normal random variates.

Or maybe random variates from a truncated normal distribution, depending on the object of the exercise.

 

[shg]

... and a SD of 150 * 0.5 * (1 - 0.5) ~ 6.124.

That should say

... and a SD of SQRT(150 * 0.5 * (1 - 0.5)) ~ 6.124