Random Number generator

Kosman

New Member
Joined
Jan 2, 2016
Messages
6
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!
 
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.

No. For one table, it would give you 75 0s and 1s only on average, IIRC.

That would give you 0 to 150 1s (the rest 0s, obviously) according to the binomial distribution, which approaches a normal distribution for large n.

But you wrote: "i do not want to make the 50 tables by hand". It is unclear how you want your 50 matrices organized and what shg had in mind. But the simplest way is to put =RANDBETWEEN(0,1) into 50 columns by 150 rows and use OFFSET or INDEX:INDEX to select individual matrices, if you must.

I'll have to let someone else provide the details, if that appeals to you. I don't have time at the moment.

Do you mean to use BINOMDIST(1,150,0.5,1) 150 times?

No. IIRC, BINOMDIST(n,150,0.5,1) is the probability of n or fewer 1s out of 150. so 1-BINOMDIST(n,150,0.5,1) is the probability of n or more (technically just "more"). With n=1, that's the probability of one or more 1s without making an effort to "guarantee" it. Pretty close to 100%.

But as I said, I might remember the probability formula incorrectly. I don't have time to double-check.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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