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%​

 

[earthworm]

Still not clear . Please highlight the result that you require and share excel file.

 

[bobsan42]

The thing with volatile functions is that they recalculate on each change in the workbook. I don't think it is easily doable without code.
Plus the idea of random is to be random

 

[earthworm]

The thing with volatile functions is that they recalculate on each change in the workbook. I don't think it is easily doable without code.
Plus the idea of random is to be random

I don't see a pattern in your data.

 

[bobsan42]

Try this formula in A6 (starting not earlier than row 6 - you will need 5 cells above for the range in the formula):

=IF(OR(COUNTIF(A1:A5,1)=5,COUNTIF(A4:A5,0)=2),RANDBETWEEN(0,1),A5)

 

[earthworm]

Do

Try this formula in A6 (starting not earlier than row 6 - you will need 5 cells above for the range in the formula):

=IF(OR(COUNTIF(A1:A5,1)=5,COUNTIF(A4:A5,0)=2),RANDBETWEEN(0,1),A5)

Do you want to count the combination of 01 and 10 only ? based on data data being entered above ?

 

[bobsan42]

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)

 

[earthworm]

Please share the sample and manually highlighted the result that you require for more clear understanding.

 

[bobsan42]

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

 

[AweSome15]

Please share the sample and manually highlighted the result that you require for more clear understanding.

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