Formula for random yes or no, with fixed % of yes and no

[elmacay]

Hi all,

I'm looking for a formula for two things. The first is that I want to return a random yes or no for any record, based on a certain percentage of yes's. So for a 100 records, with that yes variable at 10%, I'd like to get exactly 10 times yes and 90 times no.

Then, for the 10% yes's, I'd like to return a random number between 1 and 12, and if possible with an exact proportion of 1/12 for each, although with the number of records, that won't be statistically significant.

The background is that there are 7,000 social housing apartments rented out, of which 10% of tenants will move out yearly, randomly divided over the 12 months.

Any help is greatly appreciated!

Cheers, elmacay

 

[shg]

Part 1:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Pct​

[/td][td]

10%​

[/td][td]B1: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Rows​

[/td][td="bgcolor:#E5E5E5"]

20​

[/td][td]B2: =ROWS(B5:B24)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

Yeses​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td]B3: =B1*B2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

Result​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td="bgcolor:#CCFFCC"]B5: =IF(RAND() < ($B$3 - COUNTIF(B$4:B4, "Yes")) / ROWS(B5:B$24), "Yes", "No")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

Yes​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

5​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

6​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

9​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

10​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

11​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

12​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

13​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

14​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

15​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

16​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

17​

[/td][td="bgcolor:#CCFFCC"]

Yes​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

18​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

19​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

20​

[/td][td="bgcolor:#CCFFCC"]

No​

[/td][td][/td][/tr]
[/table]

I don't understand Part 2.

 

[DanteAmor]

Consider the following. Assuming 1200 houses

Formulas
[TABLE="class: grid, width: 500"]
[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]=ROUNDDOWN(COUNT(B2:B1201)*0.1,0)[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]=ROUNDDOWN(B1/12,0)[/TD]
[/TR]
[/TABLE]

Values
[TABLE="class: grid, width: 500"]
[TR]
[TD]Cell[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]D1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]1[/TD]
[/TR]
[/TABLE]

Formulas to copy down
[TABLE="class: grid, width: 500"]
[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]=RAND()[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]=INDEX($A$2:$A$1201,RANK(B2,$B$2:$B$1201))[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=IF(D1<$B$1,D1+1,"")[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=IF(D3<>"",IF(COUNTIF($E$2:E2,E2)<$C$1,E2,E2+1),"")[/TD]
[/TR]
[/TABLE]


In column C you have the house and in column E the month:

Excel Workbook
	A	B	C	D	E
1	LIST	120.000	10	0	Month
2	House1	0.21743826	House938	1	1
3	House2	0.79717274	House237	2	1
4	House3	0.07240526	House1115	3	1
5	House4	0.88980084	House114	4	1
6	House5	0.14352507	House1028	5	1
7	House6	0.43779275	House677	6	1
8	House7	0.34696654	House794	7	1
9	House8	0.8577083	House157	8	1
10	House9	0.46657688	House642	9	1
11	House10	0.5228115	House579	10	1
12	House11	0.38052232	House756	11	2
13	House12	0.38370236	House749	12	2
14	House13	0.24314565	House912	13	2
15	House14	0.00874328	House1184	14	2
108	House107	0.2854487	House863	107	11
109	House108	0.47592046	House636	108	11
110	House109	0.98669431	House11	109	11
111	House110	0.81205319	House220	110	11
112	House111	0.41658899	House716	111	12
113	House112	0.49668822	House605	112	12
114	House113	0.67130158	House402	113	12
115	House114	0.72468806	House335	114	12
116	House115	0.8429541	House176	115	12
117	House116	0.55480391	House545	116	12
118	House117	0.87106914	House138	117	12
119	House118	0.54412287	House557	118	12
120	House119	0.03514036	House1153	119	12
121	House120	0.73333774	House317	120	12
Hoja4

Check the example in the file
https://www.dropbox.com/s/9t993u24omavia1/house.xlsx?dl=0

 

[joeu2004]

=IF(RAND() < ($B$3 - COUNTIF(B$4:B4, "Yes")) / ROWS(B5:B$24), "Yes", "No")

That guarantees no more than B3 number of "yes" values.

It does not guarantee exactly B3 number of "yes" values.

The following is one example where there are no "yes" values. In fact, in one set of 2000 trails, the results were:

yes=0: 254 no=20: 254
yes=1: 954 no=19: 954
yes=2: 792 no=18: 792
total=2000
0.718750 sec

So it failed to produce exactly two "yes" values 60% of the time.


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]IF(C < D,"yes","no")<d,"yes","no")< td=""></d,"yes","no")<>[/TD]
[TD="align: right"]rand[/TD]
[TD="align: right"](B3-COUNTIF)/B2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]no[/TD]
[TD="align: right"]0.782508[/TD]
[TD="align: right"]0.100000[/TD]
[TD]B6: =IF(C6<d6,"yes","no")< td=""></d6,"yes","no")<>[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]no[/TD]
[TD="align: right"]0.678749[/TD]
[TD="align: right"]0.100000[/TD]
[TD]C6: =RAND()[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]no[/TD]
[TD="align: right"]0.573805[/TD]
[TD="align: right"]0.100000[/TD]
[TD]D6: =($B$3-COUNTIF($B$5:B5,"yes"))/$B$2[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]no[/TD]
[TD="align: right"]0.177724[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]no[/TD]
[TD="align: right"]0.134276[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]no[/TD]
[TD="align: right"]0.552324[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]no[/TD]
[TD="align: right"]0.753078[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]no[/TD]
[TD="align: right"]0.355138[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]no[/TD]
[TD="align: right"]0.207782[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]no[/TD]
[TD="align: right"]0.855179[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]no[/TD]
[TD="align: right"]0.818034[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]no[/TD]
[TD="align: right"]0.254240[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]no[/TD]
[TD="align: right"]0.790729[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]no[/TD]
[TD="align: right"]0.446269[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]no[/TD]
[TD="align: right"]0.509162[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]no[/TD]
[TD="align: right"]0.575007[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]no[/TD]
[TD="align: right"]0.375010[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]no[/TD]
[TD="align: right"]0.454331[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]no[/TD]
[TD="align: right"]0.641269[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]no[/TD]
[TD="align: right"]0.565769[/TD]
[TD="align: right"]0.100000[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Aarrgghh ! This forum mangles lines that have " < " without spaces around it. I fixed B5, but I didn't see E6 until it was too late to edit. I should be:
B6: =IF(C6 < D6, "yes", "no")

 

[shg]

That guarantees no more than B3 number of "yes" values.

It does not guarantee exactly B3 number of "yes" values.

I don't know what you did, but I would happily wager $1000 to your favorite charity that what I did gives exact results.

 

[joeu2004]

I don't know what you did, but [...] what I did gives exact results.

Yes, my bad! I did not notice the relative reference in your ROWS(B5:B$24). So if we get to B23 and there are zero "yes" values, we will have RAND() < 1, which is TRUE. And if we get to B24 and there is only one "yes" value, again we will have RAND() < 1.