Random 0/1 array with set numbers of 1s

[Augustinn]

I'm trying to generate a random distribution of 0's and 1's to a 2x10 array with the condition that a number of 1's must be a set number, let's say 15.
Here is an exact example of what I'm trying to accomplish:
- Student will be attending RANDOM 15 classes (or any number between 1 and 20)
- There are two classes per day, one in the morning, the second in the evening
- Classes will be held in 10 consecutive days
- Since student will be attending only 15 out of total 20 classes, 5 classes need to be randomly skipped
- Generate 2x10 array with the conditions above

 

[Sulprobil]

Hello,

I suggest to create a new sheet "Random_01_Array" in the file I provided the above link for, and then enter:

sbExactRandHistogrm_MrExcel_Simple_Problem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Min Half Days for Weekly Attendance	3
2	Min Half Days for Bi-Weekly Attendance	10
3	BiWeekly (%)	0,3
4	Weekly (%)	0,7
5				Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon
6	Members	Attendance	Days	Mon	Mon	Tue	Tue	Wed	Wed	Thu	Thu	Fri	Fri	Mon	Mon	Tue	Tue	Wed	Wed	Thu	Thu	Fri	Fri	Sum
7	1	Weekly	14	0	1	1	0	1	1	0	1	1	0	1	1	1	0	1	1	1	0	1	1	14
8	2	Biweekly	13	1	0	0	1	1	1	0	1	1	0	0	1	1	0	1	1	1	1	1	0	13
9	3	Biweekly	12	1	0	0	1	1	1	0	1	1	0	1	1	1	0	0	0	0	1	1	1	12
10	4	Weekly	16	1	1	1	1	1	1	0	1	0	1	1	1	1	1	0	0	1	1	1	1	16
11	5	Biweekly	12	1	1	1	1	1	1	0	1	0	0	0	0	1	1	0	1	1	1	0	0	12
12	6	Weekly	11	1	0	0	1	0	1	1	0	1	0	0	1	1	0	0	1	1	1	1	0	11
13	7	Weekly	20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	20
14	8	Weekly	4	0	0	0	0	0	0	0	0	1	1	0	1	0	0	0	0	0	0	1	0	4
15	9	Weekly	6	0	0	0	0	0	0	1	0	0	0	1	1	1	0	0	1	0	0	1	0	6
16	10	Weekly	13	1	1	0	1	0	1	0	1	0	0	1	1	1	1	1	0	1	1	1	0	13
17	11	Weekly	2	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	2
18	12	Weekly	16	1	1	1	0	1	1	1	1	1	0	1	1	1	1	0	1	0	1	1	1	16
19	13	Weekly	4	0	0	1	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	1	0	4
20	14	Weekly	6	1	0	0	1	1	0	0	0	1	0	1	1	0	0	0	0	0	0	0	0	6
21	15	Biweekly	17	1	0	1	1	1	0	1	1	1	1	1	1	1	0	1	1	1	1	1	1	17
22	16	Biweekly	13	0	1	1	0	1	1	1	1	1	0	1	1	1	0	0	1	1	0	0	1	13
23	17	Weekly	12	1	0	1	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	1	1	12
24	18	Weekly	15	0	1	0	1	1	1	1	1	0	1	1	1	0	0	1	1	1	1	1	1	15
25	19	Biweekly	10	0	0	1	0	1	1	0	1	1	0	1	1	0	1	1	0	0	1	0	0	10
26	20	Weekly	11	1	1	0	0	0	1	1	1	1	0	1	0	1	0	0	0	1	0	1	1	11
27	21	Weekly	18	1	1	0	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	18
28	22	Weekly	3	0	0	0	1	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	3
29	23	Biweekly	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1
30	24	Biweekly	11	0	1	0	0	1	1	0	1	1	1	0	0	1	0	1	1	0	0	1	1	11
31	25	Weekly	8	1	0	0	1	0	0	1	1	1	0	0	0	1	0	1	1	0	0	0	0	8
32	26	Weekly	15	0	1	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	15
33	27	Weekly	3	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	1	0	1	0	3
34	28	Biweekly	19	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	19
35	29	Weekly	13	0	1	0	1	1	0	1	1	1	0	0	1	0	1	1	1	0	1	1	1	13
36	30	Weekly	10	1	0	1	1	0	1	0	0	0	0	1	1	0	0	0	1	0	1	1	1	10
Random_01_Array

Cell Formulas
Range	Formula
B4	B4	=1-B3
B7:B36	B7	=TRANSPOSE(CHOOSE(INT(sbExactRandHistogrm(30,1,3,B3:B4)),"Biweekly","Weekly"))
C7:C36	C7	=IF(C9="Weekly", RANDBETWEEN($B$2, 10), RANDBETWEEN($B$3, 20))
D7:W36	D7	=INT(sbExactRandHistogrm(20,0,2,SEQUENCE(1,2,20-C7,20-2*(20-C7))))
X7:X36	X7	=SUM(D7:W7)
Dynamic array formulas.

Regards,
Bernd

 

[Augustinn]

Hello,

I suggest to create a new sheet "Random_01_Array" in the file I provided the above link for, and then enter:

sbExactRandHistogrm_MrExcel_Simple_Problem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Min Half Days for Weekly Attendance	3
2	Min Half Days for Bi-Weekly Attendance	10
3	BiWeekly (%)	0,3
4	Weekly (%)	0,7
5				Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon	Morning	Afternoon
6	Members	Attendance	Days	Mon	Mon	Tue	Tue	Wed	Wed	Thu	Thu	Fri	Fri	Mon	Mon	Tue	Tue	Wed	Wed	Thu	Thu	Fri	Fri	Sum
7	1	Weekly	14	0	1	1	0	1	1	0	1	1	0	1	1	1	0	1	1	1	0	1	1	14
8	2	Biweekly	13	1	0	0	1	1	1	0	1	1	0	0	1	1	0	1	1	1	1	1	0	13
9	3	Biweekly	12	1	0	0	1	1	1	0	1	1	0	1	1	1	0	0	0	0	1	1	1	12
10	4	Weekly	16	1	1	1	1	1	1	0	1	0	1	1	1	1	1	0	0	1	1	1	1	16
11	5	Biweekly	12	1	1	1	1	1	1	0	1	0	0	0	0	1	1	0	1	1	1	0	0	12
12	6	Weekly	11	1	0	0	1	0	1	1	0	1	0	0	1	1	0	0	1	1	1	1	0	11
13	7	Weekly	20	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	20
14	8	Weekly	4	0	0	0	0	0	0	0	0	1	1	0	1	0	0	0	0	0	0	1	0	4
15	9	Weekly	6	0	0	0	0	0	0	1	0	0	0	1	1	1	0	0	1	0	0	1	0	6
16	10	Weekly	13	1	1	0	1	0	1	0	1	0	0	1	1	1	1	1	0	1	1	1	0	13
17	11	Weekly	2	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	2
18	12	Weekly	16	1	1	1	0	1	1	1	1	1	0	1	1	1	1	0	1	0	1	1	1	16
19	13	Weekly	4	0	0	1	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	1	0	4
20	14	Weekly	6	1	0	0	1	1	0	0	0	1	0	1	1	0	0	0	0	0	0	0	0	6
21	15	Biweekly	17	1	0	1	1	1	0	1	1	1	1	1	1	1	0	1	1	1	1	1	1	17
22	16	Biweekly	13	0	1	1	0	1	1	1	1	1	0	1	1	1	0	0	1	1	0	0	1	13
23	17	Weekly	12	1	0	1	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	1	1	12
24	18	Weekly	15	0	1	0	1	1	1	1	1	0	1	1	1	0	0	1	1	1	1	1	1	15
25	19	Biweekly	10	0	0	1	0	1	1	0	1	1	0	1	1	0	1	1	0	0	1	0	0	10
26	20	Weekly	11	1	1	0	0	0	1	1	1	1	0	1	0	1	0	0	0	1	0	1	1	11
27	21	Weekly	18	1	1	0	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	18
28	22	Weekly	3	0	0	0	1	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	3
29	23	Biweekly	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1
30	24	Biweekly	11	0	1	0	0	1	1	0	1	1	1	0	0	1	0	1	1	0	0	1	1	11
31	25	Weekly	8	1	0	0	1	0	0	1	1	1	0	0	0	1	0	1	1	0	0	0	0	8
32	26	Weekly	15	0	1	1	1	1	1	1	0	0	1	1	1	1	1	0	0	1	1	1	1	15
33	27	Weekly	3	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	1	0	1	0	3
34	28	Biweekly	19	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	19
35	29	Weekly	13	0	1	0	1	1	0	1	1	1	0	0	1	0	1	1	1	0	1	1	1	13
36	30	Weekly	10	1	0	1	1	0	1	0	0	0	0	1	1	0	0	0	1	0	1	1	1	10
Random_01_Array

Cell Formulas
Range	Formula
B4	B4	=1-B3
B7:B36	B7	=TRANSPOSE(CHOOSE(INT(sbExactRandHistogrm(30,1,3,B3:B4)),"Biweekly","Weekly"))
C7:C36	C7	=IF(C9="Weekly", RANDBETWEEN($B$2, 10), RANDBETWEEN($B$3, 20))
D7:W36	D7	=INT(sbExactRandHistogrm(20,0,2,SEQUENCE(1,2,20-C7,20-2*(20-C7))))
X7:X36	X7	=SUM(D7:W7)
Dynamic array formulas.

Regards,
Bernd

Bernd, thank you so much. This is a very elegant solution and it works flawlessly for any inputs. Your custom functions should be standard for MS excel.