If your involves sampling, research, taking polls or working with control groups, then knowing how to randomly assign groups is a key skill.
Random assigning of groups ensures each participant or subject has an equal chance of being placed in any group and differences between and within the groups is not systematic at the outset of the experiment.
For example if you have 12 participants and you would like to assign them to 3 groups “A”,”B” and “C”, just use CHOOSE and RANDBETWEEN functions as shown below
How this Works;
CHOOSE function uses a number to return a value from the list of value arguments.
RANDBETWEEN supplys CHOOSE with a random number given a range.
Therefore to randomly assign groups;
RANDBETWEEN is a volatile function and recalculates evertime the worksheet is open.
To avoid this;
►Go to→File→Options→Formulas→Select Manual calculation→Untick Recalculate Workbook
Create a Macro to recalculate the worksheet
►Go to→Developer→Visual basic→Right click sheet→Module→Paste below code
Finally Insert a shape and assign it the Macro
►Go to→Insert→Shapes→Right-click Shape→Assign Macro