Home > Mobile >  How to create a table of random 0's and 1's (with weights) in Excel?
How to create a table of random 0's and 1's (with weights) in Excel?

Time:02-25

I would like to create a random, square table of 0's and 1's like so

0  1  0
0  0  1
0  1  0

but only bigger (around 14 x 14). The diagonal should be all 0's and the table should be symmetric across the diagonal. For example, this would not be good:

0  1  1
0  0  0
0  1  0

I would also like to have control over the number of 1 's that appear, or at least the probability that 1's appear.

I only need to make a few such tables, so this does not have to be fully automated by any means. I do not mind at all doing a lot of the work by hand.

If possible, I would greatly prefer doing this without coding in VBA (small code in cells is okay of course) since I do not know it at all.

CodePudding user response:

Edit: amended so as to return a symmetrical array, as requested by the OP.

=LET(λ,RANDARRAY(ξ,ξ),IF(1-MUNIT(ξ),GESTEP(MOD(MMULT(λ,TRANSPOSE(λ)),1),1-ζ),0))

where ξ is the side length of the returned square array and ζ is an approximation as to the probability of a non-diagonal entry within that array being unity.

As ξ increases, so does the accuracy of ζ.

  • Related