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

 

[jasonb75]

This will do what you have asked for, please note that all steps must be carried out correctly and in the order shown otherwise it is likely to fail.

First of all (in Excel) go to File > Options > Formulas and check the box to Enable iterative calculation (top right of the options list).

Next, enter this formula into a single cell which should be the top left cell of a 2x10 array of empty cells.

Note that the 2 references to A2 in the formula must be changed to the cell which you are entering the formula into.

=IF(SUM(A2#)=15,A2#,RANDARRAY(2,10,0,1,1))

Be sure not to delete the # symbols when editing the cell references.

 

[Sulprobil]

Hello,

I suggest to use this:

Simple problem I can't solve

Hello, all, I face what I think is a simple problem, yet everything I’ve tried, including a fair amount of research online, has failed. I have a spreadsheet in which a random number generates a tip for restaurant servers of $5 or $10. I’d like to collect those tip amounts in a series of cells...

www.mrexcel.com

Regards,
Bernd

 

[Augustinn]

This will do what you have asked for, please note that all steps must be carried out correctly and in the order shown otherwise it is likely to fail.

First of all (in Excel) go to File > Options > Formulas and check the box to Enable iterative calculation (top right of the options list).

Next, enter this formula into a single cell which should be the top left cell of a 2x10 array of empty cells.

Note that the 2 references to A2 in the formula must be changed to the cell which you are entering the formula into.

=IF(SUM(A2#)=15,A2#,RANDARRAY(2,10,0,1,1))

Be sure not to delete the # symbols when editing the cell references.

Thanks for helping.
It works but only for some values. It's weird. It will work for values between 6 and 15, but not above 15 or below 6. I modify the formula to array 1:20, but it does not work with 2:10 either. See the last column. Greens are OK, rest are wrong. Any suggestions?

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

Cell Formulas
Range	Formula
D9:D38	D9	=IF(B9<(30*$D$4), "BiWeekly", "Weekly")
E9:E38	E9	=IF(D9="Weekly", RANDBETWEEN($D$2, 10), RANDBETWEEN($D$3, 20))
H9:AA38	H9	=IF(SUM(H9#)=$E9,H9#,RANDARRAY(1,20,0,1,1))
AC9:AC38	AC9	=SUM(H9:AB9)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AC9:AC108	Cell Value	=$E9	text	NO

 

[jasonb75]

It will work for values between 6 and 15

It's working for me from 4 to 17, bearing in mind that at the extremities or the range you are looking at more of a bias toward one of the 2 options than a random selection.

Also, the original question asked for a random selection based on what appeared to be a defined number to choose from a pool (15 from 20 was the primary example). I haven't done extensive testing but it appears that the randbetween formula in column E reduces the chance of the array returning the desired result.

You could try going back to the options tab and increasing the number of iterations, this should improve the chance of the correct results but the increased number of calculation cycles would likely come with a cost of increased processing time.

 

[Augustinn]

It's working for me from 4 to 17, bearing in mind that at the extremities or the range you are looking at more of a bias toward one of the 2 options than a random selection.

Also, the original question asked for a random selection based on what appeared to be a defined number to choose from a pool (15 from 20 was the primary example). I haven't done extensive testing but it appears that the randbetween formula in column E reduces the chance of the array returning the desired result.

You could try going back to the options tab and increasing the number of iterations, this should improve the chance of the correct results but the increased number of calculation cycles would likely come with a cost of increased processing time.

Unfortunately, even with Maximum iterations at 10000, only few results match. Any other ideas?

 

[Augustinn]

And smaller array causes more mismatches.

 

[jasonb75]

My results seem to be a bit closer than yours were and I only set it to 1000 so I guess it means that the number of matching results from a random array is random. I don't see that you will be able to make it any more accurate with formulas alone, have you looked at @Sulprobil's alternative suggestion?

 

[Augustinn]

I tried @Sulprobil solution, but it is something that will not work either.

 

[jasonb75]

I'm afraid that I'm out of ideas then, the method that I suggested worked for me in simplified testing based on the original question but it seems unable to cope with the scope of the variables required for the full task.