Equally divide 0's and 1's in a Row of 8

[Yashish]

Hello There,

I need help in generating a random number sequence table in groups of 4 with 8 blocks (totaling to 32).
Is there a formula which could divide the random 0's and 1's in row of 8.
I have attached an example. Kindly assist me.

 

[Luke M]

Hi Yashish, and welcome to the forum.

Can you elaborate on what you mean by dividing the 0's and 1's? In your example file, I see two grids, and there's only a single 1 on entire sheet. Are we dividing that number somehow? Perhaps a different example showing what you have now, and then on the 2nd sheet, what you would like to see?

 

[Hui]

Yashish

Firstly, Welcome to the Chandoo.org Forums

In A2: =LARGE(ROW($1:$100)*NOT(COUNTIF($A$1:A1,ROW($1:$100))),RANDBETWEEN(1,(100+2-1)-ROW(A1))) Ctrl+Shift+Enter
Copy across and down

See attached file:

Not really sure what "Is there a formula which could divide the random 0's and 1's in row of 8." means?

 

[Yashish]

I know we can generate random numbers using Randbetween formula with which I will get 0 and 1 but can we equally allocate these 0's and 1's in such a way that in a row of 8( 1 should be displayed 4 times and 0 should be displayed 4 times) and again it should be in random order.
I am attaching the example which I tried to achieve using Randbetween but it wasn't allocated equally, so I manually made 0's and 1's to display 4 times each.

 

[Hui]

How about this?

I needed a helper column which is over at Column U
You can hide those if you wish

I used a formula which extracts a number using choose from a list of Numbers
=DEC2BIN(CHOOSE(RANDBETWEEN(1,6),3,5,6,9,10,12),4)
This list is special in that it is the list of Decimals that have 2 1's when converted to Binary

Then i just extract the each digit using a Mid formula

See attached file

 

[Yashish]

Hello Hui,

Thanks for the response, I guess u attached the initial file what I had attached

Regards,
Yash

 

[Hui]

Whoops, so I did

 

[Yashish]

Whoops, so I did

Thanks Hui,

It seems to be working but only problem is every column in each site should have four 0's and four 1's.
For eg:- The attached file provided by you in Site 1 A2 to A8 was showing only two 0's and six 1's. I hope you are understanding the requirement and sorry in case I confused you.

 

[Hui]

Are the rows required to have 2 0's and 2 1's
or the column 4 0's and 4 1's
or both ?

 

[Yashish]

Are the rows required to have 2 0's and 2 1's
or the column 4 0's and 4 1's
or both ?

If u can see from the below screenshot each column has four 0's and four 1's

 

[Hui]

Use the same logic but adapt it to the columns

 

[Yashish]

Use the same logic but adapt it to the columns

Hello Hui,

I am sorry but was unable to adapt it to columns. I know its almost solved, could you please assist me in adopting the logic to columns.

 

[Hui]

Same technique but this time we have to use numbers up to 256 (2^8)
There are 70 Binaries between 0 and 256 that have 4 x 1's
So the formula becomes:
=DEC2BIN(CHOOSE(RANDBETWEEN(1,70),15,23,27,29,30,39,43,45,46,51,53,54,57,58,60,71,75,77,78,83,85,86,89,90,92,99,101,102,105,106,108,113,114,116,120,135,139,141,142,147,149,150,153,154,156,163,165,166,169,170,172,177,178,180,184,195,197,198,201,202,204,209,210,212,216,225,226,228,232,240),8)

see attached file:

Press F9 to get new numbers if required

 

[r1c1]

Hui, awesome formula...

Here is one more variation, that avoids hardcoding of the values in CHOOSE.

=DEC2BIN(LARGE($A$26:$A$281*$C$26:$C$281, RANDBETWEEN(1,70)),8)

 

[Hui]

Chandoo,

A26 to A281 won't exist in the final file
they are only there to show the OP where the numbers came from