Advanced Features

Responses.xls: Calculated Values, Complex Skips & Reports


This section will show you how to accomplish three advanced functions with MediaLab:

Calculate scores and other variables while a session is running. Present these calculated values on screen as stimuli or feedback, use them in skip patterns, or in post-session reports.

Execute complex skip patterns. Base skips on any prior response, or even combinations of prior responses and calculated variables.

Create and optionally print summary reports immediately following the experimental session. Create graphs, scale scores, or anything else you want based on raw and/or summarized data.


Before we look into the technical details of accomplishing these advanced functions, let's run a quick sample that demonstrates all three. Note that to take advantage of the functions described in this section, you will need to have Microsoft Excel installed on your system—both to design and to run the sessions. Also, because these are considered advanced features, we're going to presume you have some basic familiarity with MediaLab.

From the samples folder, run Condition 1 of the following experiment:

C:\MediaLab\Samples\Sample5 advanced features\advanced.exp

After the first few questions, this should appear to look just like the standard self-esteem questionnaire sample from the introductory tutorial.

What is very different from the standard tutorial is the summary screen which follows the self-esteem scale. Somehow, we have been able to calculate your overall self-esteem score and present that value back to you. We have also been able to tell you what that score means—i.e., whether you are high or low in self-esteem—complete with matching emoticon—i.e., :) or :(.

We have also been able to calculate your average response time and tell you if you've been fast or slow. On the following screen we continue by asking a question about your membership in a racial minority or majority—based on how you responded to the race question earlier in the questionnaire.

What happens next will depend on how you scored on the self-esteem test. You can try this a couple times to see for yourself. If you scored moderately (2, 3, or 4), then you will be at the end of the session. If you scored very high (greater than 4) then you will be at a screen representing where a social desirability scale might be administered. If you scored very low (less than 2) then you will be at a screen representing where a depression inventory might be administered.

When you're done, go to the experiment's data folder. In here, you'll find a folder called "reports". Inside the reports folder is a file called 1.xls (this will be different if you used another subject ID). Open this file by double-clicking on it. First you'll see many of the responses you gave to the questionnaire. Click on the tab at the bottom left that says "sheet 2". Here you'll find a custom report for the session that displays your name, age and self-esteem score. It presents a graphic that is based on your self-esteem score and also displays a graph of the individual response times for each question. This report is completely customizable and can be created automatically for each subject—with any raw or summary data you like, and formatted in any way you like.

How is it all done?

Let's close the report file and go back to the experiment folder. Open the file responses.xls—this is the secret. If you have a standard Excel file called responses.xls in your experiment folder then MediaLab knows you want to use some or all of these advanced functions. The question now—what do we put in this Excel file called responses.xls?

Variable, Value and Skipto

Notice that there are three columns on the opening worksheet—variable, value, and skipto.

The Variable Column

In the variable column there are two types of variables—experiment variables and calculated variables. If you take a look in our questionnaire self-esteem.que you'll see that many of the variables listed there are also listed here in the variable column of responses.xls—name, age, rse1, rse2, etc. These are experiment variables—i.e., variables taken from any of our questionnaire files in the experiment. This also includes reaction times such as trse1, trse2, trse3 and so on. If it would normally appear in your regular MediaLab data files, then it can be used as an experiment variable in the responses.xls file.

But what about variables like minmaj, rse (positive scored), rse (positive scored), and rse? These are not variables from the experiment. That's because they are calculated variables we are creating them here in the responses.xls file. You can create any and as many new calculated variables as you like and you can call them whatever you like. The only rule for a calculated variable is that it has a name that isn't used in your experiment.

The Value Column

This brings us to the value column. For experiment variables, this is really simple—you don't need to do anything. As a subject progresses through your experiment, MediaLab will automatically fill these values in with whatever value is recorded for that variable in the regular data files. After every question in your experiment, MediaLab checks the variable column of the responses.xls file to see if it can fill in any values with the newly acquired data. Thus, experiment variables are automatically updated by MediaLab when the data become available.

What happens next is the really fun part–Excel automatically updates all of your calculated variables taking into account the current values of your experiment variables!

Let's look at the example of rse (positive scored) . Double-click the mouse on the value column for this calculated variable. You'll see that this cell actually contains the formula:


This is an Excel calculation formula. We're telling Excel that we want the value here to equal the average of the cells listed in parentheses. If you look at what's in those cells, you'll see that it's the subject's responses to rse1, rse2, rse4, rse6, and rse7. These happen to be the positively scored self-esteem items such as "I feel that I have a number of good qualities."

Now let's look at rse (negative scored) . You'll see that this cell contains:


Here we're averaging the reverse scored items (e.g., "I am inclined to feel that I am a failure.") and then subtracting that value from 7 to recode it such that higher numbers reflect higher self-esteem.

Finally, look at rse :


We see that the final calculated self-esteem score is an average of the two sub-scores above. Now we have a calculated variable rse that is the subject's overall self-esteem score.