# random assignment

#### Mbouas

##### New Member
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?

### 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
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
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
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

##### New Member
Thank you very much!!

Replies
0
Views
32
Replies
0
Views
53
Replies
5
Views
256
Replies
66
Views
1K
Replies
1
Views
607