A benefit of excel is the easy creation of a repeated series of text, and the subsequent, instant, updating of that text. Using excel to create the repeated items in DMDX is a powerful way of creating large experiments, which are easily updateable and not prone to human error (imagine typing in 100 lines of code for a file by hand, now there’s going to be a mistake in there somewhere). Its easy to do and will save you time.
So how do you use this? I’ve divided this helpfile into two halves. The first half looks at the creation of the excel file. The second half shows you how to import the created file into a suitable format (rtf) for DMDX to use.
Making a script file in excel
- Open up excel, and you will get a window similar to the one below.
- I usually devote an area of the Excel book to information on trials (TRIAL INFO), and another on the creation of the DMDX script file (SCRIPT INFO). So, for my example experiment I will have TRIAL INFO on the left, and SCRIPT INFO on the right. Below is a really simple experiment where the lettering of a colour and the colour of that lettering are rotated. Every possibility of (green, blue, red, white) and Text (same again) are in the experiment and there are 12 trials.
- The right hand area (SCRIPT INFO) is to hold the actual writing that DMDX needs to run. There are some important considerations when you are creating this area. In a normal DMDX file, there is a considerable amount of repeated text between each item. Portions of this repeated text can go into one cell in the excel file, and I’ll now attempt to explain where they go. We will work with just one line (one trial) for the time being, which, consists of the following text:
=1 * <r 000000255> “Green” /;
All the trials share some text with the first trial and this is shown below (the x’s representing the information that changes in each trial):
=X * <r XXXXXXXXX> “XXXXX” /;
Each ‘clumping’ of repeated information can go in a cell, and the changing information needs its own separate cell. Thus we need 5 cells for the first trial, which hold all the information above.
|=X * <r||XXXXXXXXX||> “||XXXXX||
For the X’s above, you need to reference the particular cell with the correct information in the left hand side of the column. The clever thing about excel is that you can tell cells in one area to portray the contents of another cell. Above, cell L17 is getting its information from cell D14 (using the formula “=D14” in cell L17), and cell J21 is getting its information from C18 (using the formula “=C18” in cell J21).
Repeating your first item text for the entire experiment
- Once you have created your first item in the right hand side, to copy the repeated text, and to correctly reference the differing information to the correct cells in the left hand column, all you need to do is highlight the contexts of the correct item, select the bottom right corner of the highlight and drag down the contents (the small square on the selected blue area). Excel, will automatically update your references, which saves you a tonne of time. See the below pic sequence below:
NOTE: Excel can also automatically repeat a sequence of cells. By highlighting your sequence, and dragging out the box as mentioned above, that sequence will be repeated. This is especially useful during the creation of your Trail Information column.
The final step
Once you’ve created your entire experiment there is one more step before you can export your work. You need to tell excel to join together all the information in the however many cells. This is done using the ‘Concatenate’ function for each row of information.
So, in the file above, we need to put:
=concatenate (F6, G6, H6, I6, J6)
in P6. This will join all the contents of the cells together. There are some facts to consider though.
- You may need to add the occasional SPACE in some of your excel cells. Just browse through the new rows created by the concatenate function (on the right hand side above) to make sure everything is in the right place. For example, for tidiness, cell H6 should be ” * ” and not just “*”. Sometimes its easy to put in an accidental SPACE, which will need to be removed
- The concatenate function can join together a maximum of 30 cells. If you need more, simply use multiple concatenate functions.
Randomising your experiment through excel
Now that you have created your experiment, you can change the order in your experiment information area, and the experiment will automatically update. One benefit of this is the ability to randomise the order of your experiment. Even though DMDX is 1000 times better at this sort of thing (using the scramble function), sometimes you need to control how your trials are randomised and this is the perfect way to do it.
- Create a new column next to your experiment information (here, right click on the “E” and select “insert”) and add next to each trail the formula:
- Now, select all the cells, from “0.147056” in the bottom right hand corner, to “1” in the top left hand corner. Goto “Data” (one of the options on the menu bar), and click “sort…”.
- The above screen will pop up. Make sure the correct column has been selected for the sort (click on the box below “sort by” to make sure) and then click OK.
- Your experiment has been randomly sorted, and by following the steps in the section “Converting the excel file to a file readable by DMDX”, you got a randomly ordered experiment, within a few clicks.
Other things you can do:
- Create blocks of scrambled trials by selecting the trials you want to have in a block and randomising these only.
- Another way to do this is to add another column, and allocate your trials into blocks by numbering them in this column. Then when you sort your trials (see 3.), sort by this new column first, and then by your column of random numbers. DMDX can easily do this, but doing it though excel does have benefits: it allows for the easy creation of
Converting the excel file to a file readable by DMDX
- Select the entire column where you used the concatenate function (just click the grey letter at the top of the column). Copy the contents (Edit, Copy).
- Open WordPad. Select Edit from the menu, then Paste Special. Select Rich Text (RTF) from list of options and click OK. See the picture below.
- Save the file as an RTF and see if it works in DMDX. NOTE: A massive benefit of using Wordpad is that you can have your script open in Wordpad and DMDX at the same time. This is really useful during the ‘debugging stage’.