Generate Random Binary Number within specific requirement i.e. percentage etc.

[AweSome15]

Hello everyone,
I wrote formula to generate binary number (0,1), as obviously it had generated as I expected (as can be seen). My questions are:
1. How can I formulate, whenever it generated '0', it would stay for at least/minimum two rows (included the generated number), but when it generated '1' it would stay for at least five rows (included the generated number). Whatever number after the minimum requirement fulfilled will be ignored
2. Is it possible to generate binary number (0,1) (as i pointed out in the 1st question) while adding another requirement i.e., the '0' or '1' number within specific percentage (let say 20 %) and the rest will automatically follows on the portion to it.

Most appreciated to all of you,
Cheers Up

=RANDBETWEEN((0,1)	Expected		=RANDBETWEEN((0,1)	Expected
0	0		1	1
1	0		1	1
0	0		1	1
1	1		1	1
1	1		1	1
1	1		1	1
0	1		0	0
1	1		0	0
1	1		0	0
1	1		0	0
0	0		0	0
1	0		1	1
1	1		0	1
% of Zeros	38.46%​		% of Zeros	38.46%​
% of non-Zeroes	61.54%​		% of non-Zeroes	61.54%​

 

[AweSome15]

About the secont part of your question:
You will need a code finding such numbers or a setup similar to the one below (I made it for 30% Zeros because 20% is quite low and may require a lot of space to find one):

FramesScan.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
6			0	0	1	0	1	0	1	0	1	0	0	1	0	1
7			0	0	1	0	1	0	1	0	1	0	0	1	0	1
8			1	0	1	1	1	0	1	1	1	1	0	1	1	1
9			1	1	1	1	1	0	1	1	1	1	0	1	1	1
10			1	1	1	1	1	0	1	1	1	1	1	1	1	1
11			1	1	1	1	0	1	0	1	1	1	1	1	1	0
12			1	1	1	1	0	1	0	1	0	1	1	0	1	0
13			0	1	0	1	1	1	1	0	0	1	1	0	0	1
14			0	0	0	0	1	1	1	0	0	0	1	0	0	1
15			0	0	0	0	1	1	1	0	0	0	0	0	1	1
16			1	0	1	0	1	1	1	1	0	1	0	1	1	1
17			1	1	1	0	1	0	1	1	0	1	1	1	1	1
18			1	1	1	1	0	0	0	1	0	1	1	1	1	0
19			1	1	1	1	0	1	0	1	1	1	1	1	1	0
20			1	1	1	1	0	1	1	1	1	1	1	1	0	0
21			1	1	1	1	1	1	1	0	1	0	1	0	0	1
22			0	1	1	1	1	1	1	0	1	0	1	0	0	1
23			0	0	0	1	1	1	1	1	1	1	1	0	0	1
24			0	0	0	1	1	0	1	1	0	1	1	0	0	1
25			1	1	1	0	1	0	1	1	0	1	1	1	0	1
26			1	1	1	0	1	1	0	1	1	1	0	1	1	0
27			1	1	1	1	0	1	0	1	1	1	0	1	1	0
28			1	1	1	1	0	1	1	0	1	1	1	1	1	1
29			1	1	1	1	1	1	1	0	1	1	1	1	1	1
30			0	1	1	1	1	1	1	0	1	0	1	0	1	1
31			0	1	0	1	1	1	1	1	0	0	1	0	1	1
32			1	1	0	0	1	0	1	1	0	1	1	0	0	1
33			1	1	1	0	1	0	1	1	1	1	1	1	0	1
34			1	0	1	0	1	1	1	1	1	1	1	1	1	1
35			1	0	1	1	0	1	1	1	1	1	1	1	1	0
36
37	0s:	30%	33.33%	33.33%	23.33%	36.67%	26.67%	36.67%	20.00%	33.33%	36.67%	26.67%	26.67%	36.67%	40.00%	26.67%
RAND

Cell Formulas
Range	Formula
G6:T35	G6	=IF(OR(COUNTIF(G1:G5,1)=5,COUNTIF(G4:G5,0)=2,ISBLANK(G5)),RANDBETWEEN(0,1),G5)
G37:T37	G37	=COUNTIF(G6:G35,0)/COUNT(G6:G35)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G37:AC37	Cell Value	<=$F$37	text	NO

Hi, Bob
Thanks for the reply....
What i mean is, what ever binary numbers generated in the A column, while still fulfilling the requirement of what I previously explained. So, I can either select the exact percentage of '0' or '1' in the B column. Let's say, i would select the '0' for 40% of fifty generated-number (A2:A51), so the total number of '0' would be twenty of zeroes number of which arranged at least/minimum two rows in a series.

Hope this clarify my problem statement
Many Thanks

 

[AweSome15]

Sorry, I stand corrected. The previous formula will always get you started with a Zero.
Try this one:
=IF(OR(COUNTIF(A1:A5,1)=5,COUNTIF(A4:A5,0)=2,ISBLANK(A5)),RANDBETWEEN(0,1),A5)

FramesScan.xlsm
	A	B	C	D
1
2
3
4
5
6	1	0:	9	23.08%
7	1	1:	30	76.92%
8	1	tot:	39
9	1
10	1
11	1
12	0
13	0
14	0
15	1
16	1
17	1
18	1
19	1
20	0
21	0
22	1
23	1
24	1
RAND

Cell Formulas
Range	Formula
D6	D6	=C6/C8
D7	D7	=C7/C8
C6	C6	=COUNTIF(A:A,0)
C7	C7	=COUNTIF(A:A,1)
C8	C8	=COUNT(A:A)
A6:A24	A6	=IF(OR(COUNTIF(A1:A5,1)=5,COUNTIF(A4:A5,0)=2,ISBLANK(A5)),RANDBETWEEN(0,1),A5)

I have tried this formula, but still not of what I expected yet. I realized that you started from the 6th row since the formula needs a reference. Yes, its okay if it starts from zero ('0') as default initialization.

Most appreciated to you,
Thanks

 

[earthworm]

Hi, EartWorm really appreciated for the reply.
Sorry, i still could not use XL2BB function. So please take a look for the screenshot. So the idea is by running the RAND function to generate random binary numbers (lets say 50 numbers from A2:A51), then the expected result next to it (B2:B51), under conditions of:
1. If it generates '1' (A2) then it will stay for at least five rows in total (B2:B6), and when it generates '0' (A6) then it will stay for at least two rows (B7:B8)
2. What ever number after the requirement is fulfilled (B14:B15), what ever number next to it if the same the previous (0 at A:16) it would be stayed otherwise has to stop due to change (1 at A:17). Thus, it will fulfill the requirement as mentioned in the 1st.

I hope this may clarify my problem statement :D

View attachment 45511

Looks simple but not easy . let me do some experiments

 

[earthworm]

This is next to impossible because there is no fixed pattern .