random assignment

Mbouas

New Member
Joined
Apr 9, 2018
Messages
3
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mopp1

Board Regular
Joined
Apr 4, 2017
Messages
74
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")"
 
Last edited:

Mbouas

New Member
Joined
Apr 9, 2018
Messages
3
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

Board Regular
Joined
Apr 4, 2017
Messages
74
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,633
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top