random assignment

[Mbouas]

Hello
i have 1001 individuals in my dataset, i would like to assign randomly to each one "owner" and "non_owner". knowing that after the random assignement i should have 71% owner and 29% non-owner. is it possible with excel?
thank you for your help

 

[mopp1]

Say your names are in Column A from A1:A1001.

In B1 put =Rand() and copy down to B1001

Then Copy B1:B1001 and paste special Values so the B column random values are fixed.

Then in C1 put
<LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner")"
<LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner")
<LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner") down.

=IF(B1 < LARGE($B$1:$B$1001,INT(0.29*1001)),"Owner","Non-Owner")

That will give you 290 random Non-Owners and 711 Owners. You can't get exactly 29% and 71% with a population of 1001.

You can change the 0.29 and the 1001 to reference cells instead of being hard wired if those values will likely change.</LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner")></LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner")
</LARGE($B$1:$b$1001,int(0.29*1001)),"owner","non-owner")"

 

[Mbouas]

Thank you, it worked. What if i had more categories like owner 78%, renter 12%, Appropriation 3%, free housing 6%, and other 1%? <large($b$1:$b$1001,int(0.29*1001)),"owner","non-owner")"

</large($b$1:$b$1001,int(0.29*1001)),"owner","non-owner")"

 

[mopp1]

Put Owner, Renter, etc. in E1:E5
Put percentages in F1:F5 (i.e., .78, .12, ...)
Put =1-SUM(F$1:F1) in G1 and copy down to G5

Change C1 to =INDEX($E$1:$E$5,MATCH(TRUE,B1<=LARGE($B$1:$B$1001,1+INT($G$1:$G$5*1001)),0))
And Enter with Ctrl-Shift-Enter (not just Enter)

Copy C1 down to C1001

If you want to see actual percentages put =COUNTIF(C:C,E1)/1001 in D1 and drag down to D5

 

[Mbouas]

Thank you very much!!