Assigning a random binary value to only a percentage of cells

[hunnel]

Hello,

I am struggling with this which should be seemingly simple.

How can I assign a random binary value to a percentage of identified cells?

I have a column that indicates which cells I want this binary value to be associated with. My problem is when trying to chose both a percentage and implementing a random element to it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Suvey ID[/TD]
[TD]Season[/TD]
[TD]Score[/TD]
[TD]Random Binary[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Peak[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peak[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Off Season[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Off Season[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Off Season[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Off Season[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peak[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Within the random binary field I want to give 70% of those within the "Off Season" category a 1 and the remaining 30% a 0. For those in the "Peak Season" I would like to assign 30% a 1 and 70% a 0.

Any advice would be of great help!

Thank you,
Dan

 

[StephenCrump]

Welcome to the Forum!

D2: =RAND()
E2: =0+(COUNTIFS(B$2:B$8,B2,D$2:D$8,"<="&D2)<=VLOOKUP(B2,B$11:C$12,2,))
C11: =ROUND(30%*COUNTIF(B$2:B$8,B11),0)
C12: =ROUND(70%*COUNTIF(B$2:B$8,B12),0)

(You may want to ROUNDUP or ROUNDDOWN instead)

<lookup(b2,{"off season","peak"},{0.5,0.5}))

Book1
	A	B	C	D	E
1	ID	Season	Score	Helper	"Random"
2	1	Peak	0	0.812	0
3	2	Peak	1	0.758	1
4	3	Off Season	5	0.871	1
5	4	Off Season	8	0.523	1
6	5	Off Season	4	0.979	0
7	6	Off Season	8	0.893	1
8	7	Peak	4	0.873	0
9
10		1's to allocate
11		Peak	1
12		Off Season	3
Sheet1

</lookup(b2,{"off>