How to get randomly assigned groups into equal amounts?

bwe89

New Member
Joined
Jul 28, 2016
Messages
4
Hi everyone,

I am using =CHOOSE(RANDBETWEEN(1,2),"A","B") to randomly assign data into two groups A & B. But when I do for 120 contacts does not group them into two equal groups of 60, which is what I want. Instead, it will have one group with 68 and the other with 52.

Does anyone know a solution to make randomly assigning groups happen in equal quantities?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,493
You could just use RAND to assign a random number to each contact, then use SMALL and group everyone below the n/2 th value into one group, and everyone else into the other:

ABCDE
1Contact 10.123612AAsBs
2Contact 20.309422A1010
3Contact 30.334354A
4Contact 40.621878A
5Contact 50.877647B
6Contact 60.777637B
7Contact 70.733063B
8Contact 80.193756A
9Contact 90.942704B
10Contact 100.156775A
11Contact 110.772948B
12Contact 120.542172A
13Contact 130.134582A
14Contact 140.784151B
15Contact 150.865734B
16Contact 160.498663A
17Contact 170.912226B
18Contact 180.847757B
19Contact 190.699894A
20Contact 200.795236B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
B1=RAND()
C1=IF(B1<=SMALL(B$1:B$20,10),"A","B")
D2=COUNTIF(C:C,"A")
E2=COUNTIF(C:C,"B")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,119,116
Messages
5,576,194
Members
412,705
Latest member
Collie
Top