# Chapter 3 Randomize Using Excel

 Page 2/3 Date conversion 13.12.2016 Size 291.74 Kb.
1   2   3

Random Permuted Blocks With Randomized Block Sizes
If an investigator is attempting to guess what group the next patient will be randomized to, and has noticed that balance appears to be accomplished every two patients in an un-blinded study, the investigator can introduce bias by decided whether or not to enroll the next patient.
One solution is to use a large block size, say 20, instead of 2. It is also a good idea to not inform the investigator that blocks are used in the randomization.
Another approach is to use multiple block sizes. For a two-arm study, you might use block sizes of 2, 4, and 6 in a random order. This would make it particularly difficult for the investigator to keep track.
To illustrate, let’s say the sample size will be 12 in each group, so we can use two each of block sizes 2, 4, and 6.
1) Start by creating the Sequence column, which establishes the sample size and provides an ID number for the order in which patients will enter the study.

 A Sequence 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

2) Copy that column and add two new columns, creating one each of the desired block sizes:

 C D E Sequence Group BlockSize 1 Intervention 2 2 Control 2 3 Intervention 4 4 Intervention 4 5 Control 4 6 Control 4 7 Intervention 6 8 Intervention 6 9 Intervention 6 10 Control 6 11 Control 6 12 Control 6 13 14 15 16 17 18 19 20 21 22 23 24

3) Copy those columns and add one new column, providing a random number in the first cell of each block, using “=rand()”.

 G H I J Sequence Group BlockSize RandNumBlock 1 Intervention 2 0.615181 2 Control 2 3 Intervention 4 0.175673 4 Intervention 4 5 Control 4 6 Control 4 7 Intervention 6 0.427291 8 Intervention 6 9 Intervention 6 10 Control 6 11 Control 6 12 Control 6 13 14 15 16 17 18 19 20 21 22 23 24

4) Copy this to new columns. The, copy the cells in the last three columns and paste just below them. Notice that the random numbers changed, which is fine (they update every time a change is made to the spreadsheet).

 L M N O Sequence Group BlockSize RandNumBlock 1 Intervention 2 0.36642 2 Control 2 3 Intervention 4 0.51709 4 Intervention 4 5 Control 4 6 Control 4 7 Intervention 6 0.73186 8 Intervention 6 9 Intervention 6 10 Control 6 11 Control 6 12 Control 6 13 Intervention 2 0.38613 14 Control 2 15 Intervention 4 0.05518 16 Intervention 4 17 Control 4 18 Control 4 19 Intervention 6 0.391385 20 Intervention 6 21 Intervention 6 22 Control 6 23 Control 6 24 Control 6

5) We are now ready to fix the random numbers so they do not change. Copy and paste the RandNum column onto itself, using “Paste Special”, as described on page 2.

6) Copy these into new columns. Then, highlight each RandNum number and drag it into the empty cells just below it, so that each row of a block has an identical random number.

 Q R S T Sequence Group BlockSize RandNumBlock 1 Intervention 2 0.36642 2 Control 2 0.36642 3 Intervention 4 0.51709 4 Intervention 4 0.51709 5 Control 4 0.51709 6 Control 4 0.51709 7 Intervention 6 0.73186 8 Intervention 6 0.73186 9 Intervention 6 0.73186 10 Control 6 0.73186 11 Control 6 0.73186 12 Control 6 0.73186 13 Intervention 2 0.38613 14 Control 2 0.38613 15 Intervention 4 0.05518 16 Intervention 4 0.05518 17 Control 4 0.05518 18 Control 4 0.05518 19 Intervention 6 0.391385 20 Intervention 6 0.391385 21 Intervention 6 0.391385 22 Control 6 0.391385 23 Control 6 0.391385 24 Control 6 0.391385

This RandNumBlock column will be used later to randomize the order of the blocks.

7) Copy these into new columns. Then, add a second column of random numbers in the first cell and drag it all the way to the bottom, filling in the entire column.

 V W X Y Z Sequence Group BlockSize RandNumBlock RandNumGroup 1 Intervention 2 0.36642 0.2407044 2 Control 2 0.36642 0.1607768 3 Intervention 4 0.51709 0.4609201 4 Intervention 4 0.51709 0.1547385 5 Control 4 0.51709 0.8725464 6 Control 4 0.51709 0.1919791 7 Intervention 6 0.73186 0.4904566 8 Intervention 6 0.73186 0.8843401 9 Intervention 6 0.73186 0.8812662 10 Control 6 0.73186 0.7756594 11 Control 6 0.73186 0.6503676 12 Control 6 0.73186 0.7629637 13 Intervention 2 0.38613 0.6910306 14 Control 2 0.38613 0.7458984 15 Intervention 4 0.05518 0.8386934 16 Intervention 4 0.05518 0.807224 17 Control 4 0.05518 0.477761 18 Control 4 0.05518 0.4581632 19 Intervention 6 0.391385 0.8722347 20 Intervention 6 0.391385 0.8057507 21 Intervention 6 0.391385 0.8545892 22 Control 6 0.391385 0.6339632 23 Control 6 0.391385 0.1114622 24 Control 6 0.391385 0.7572725

8) Copy and Paste Special Values this second column of random numbers, making them fixed

9) Copy these into new columns. Highlight all columns but the Sequence column. Then on the menu bar, click on Data, click on Sort, Sort by RandNumBlock, Then by RandNumGroup, OK.

 AB AC AD AE AF Sequence Group BlockSize RandNumBlock RandNumGroup 1 Intervention 2 0.36642 0.2407044 2 Control 2 0.36642 0.1607768 3 Intervention 4 0.51709 0.4609201 4 Intervention 4 0.51709 0.1547385 5 Control 4 0.51709 0.8725464 6 Control 4 0.51709 0.1919791 7 Intervention 6 0.73186 0.4904566 8 Intervention 6 0.73186 0.8843401 9 Intervention 6 0.73186 0.8812662 10 Control 6 0.73186 0.7756594 11 Control 6 0.73186 0.6503676 12 Control 6 0.73186 0.7629637 13 Intervention 2 0.38613 0.6910306 14 Control 2 0.38613 0.7458984 15 Intervention 4 0.05518 0.8386934 16 Intervention 4 0.05518 0.807224 17 Control 4 0.05518 0.477761 18 Control 4 0.05518 0.4581632 19 Intervention 6 0.391385 0.8722347 20 Intervention 6 0.391385 0.8057507 21 Intervention 6 0.391385 0.8545892 22 Control 6 0.391385 0.6339632 23 Control 6 0.391385 0.1114622 24 Control 6 0.391385 0.7572725

10) It now looks like this. Notice that the block sizes are jumbled up, as well as the group assignments within each block. This is what we were after.

 Sequence Group BlockSize RandNumBlock RandNumGroup 1 Control 4 0.05518 0.458163 2 Control 4 0.05518 0.477761 3 Intervention 4 0.05518 0.807224 4 Intervention 4 0.05518 0.838693 5 Control 2 0.36642 0.160777 6 Intervention 2 0.36642 0.240704 7 Intervention 2 0.38613 0.691031 8 Control 2 0.38613 0.745898 9 Control 6 0.391385 0.757272 10 Control 6 0.391385 0.111462 11 Control 6 0.391385 0.633963 12 Intervention 6 0.391385 0.805751 13 Intervention 6 0.391385 0.854589 14 Intervention 6 0.391385 0.872235 15 Intervention 4 0.51709 0.154739 16 Control 4 0.51709 0.191979 17 Intervention 4 0.51709 0.46092 18 Control 4 0.51709 0.872546 19 Intervention 6 0.73186 0.490457 20 Control 6 0.73186 0.650368 21 Control 6 0.73186 0.762964 22 Control 6 0.73186 0.775659 23 Intervention 6 0.73186 0.881266 24 Intervention 6 0.73186 0.88434

11) Finally, copy and paste the Sequence and Group columns onto a new worksheet. Do this by clicking on the Sheet2 tab, and then pasting it there.

 A B Sequence Group 1 Control 2 Control 3 Intervention 4 Intervention 5 Control 6 Intervention 7 Intervention 8 Control 9 Control 10 Control 11 Control 12 Intervention 13 Intervention 14 Intervention 15 Intervention 16 Control 17 Intervention 18 Control 19 Intervention 20 Control 21 Control 22 Control 23 Intervention 24 Intervention

Right click on the Sheet2 tab and rename it to “Randomization List”. Right click on the Sheet1 tab and rename it “Setting up Randomization”. You keep this Excel File.

After you have saved your file, which has both sheets, you right click on the “Setting up Randomization” tab, and select “Delete”. You then give a spreadsheet with just the Randomization List sheet to the person responsible for the randomization.
One way the randomization can be carried out is having a piece of paper that contains just “Intervention” or “Control” placed inside envelopes. On the outside of the envelopes put the sequence number. After a patient has been enrolled into the study and consented, the next envelope on the stack is opened and that is the group the study subject goes into.
Not giving anyone else the worksheets that contain the block size guards against the possibility that someone will include the Blocksize column in the envelopes.
Part way into the study, and at the end, you can audit the randomization process by making sure the group assignment order that goes with the enrollment date and time that is recorded on the data collection form (case report form) or in the database, corresponds with your randomization list.

References

Friedman LM, Furberg CD, DeMets DL. (1998). Fundamentals of Clinical Trials, 3rd ed.,

New York, Springer, pp.64-66.
Kirkley A, Birmingham TB, Litchfield RB, et al. (2008). A randomized trial of arthroscopic

surgery for osteoarthritis of the knee. N Engl J Med 359(11):1097-107.

Kuhn L, Aldrovandi GM, Sinkala M., et al. (2008). Effect of early, abrupt weaning on HIV-free

survival of children in Zambia. N Engl J Med 359(2):130-41.

1   2   3