Chapter 3 Randomize Using Excel



Download 291.74 Kb.
Page2/3
Date conversion13.12.2016
Size291.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


The database is protected by copyright ©dentisty.org 2016
send message

    Main page