Using PE_DataAnalysis2.xls
To Analyze Your Data in Excel 2002 (XP),2003

For the convenience of  users who don't want to write custom programs to process data from the PsychExperiments data archive,  we offer a free Excel  file that contains a VisualBasic program (often referred to as a macro1)  that will  format and process the data in ways that will meet most users' needs.   The program is generic in that the one program processes data from each of the experiments.  The Excel file with the program can be downloaded at the same time you download data.

Why Excel?  Because Excel is the most widely used spreadsheet application in the market.  We believe, therefore, that most users will not need to buy new software just to use our data.   In addition, Excel happens to be an excellent spreadsheet program. It allows you to produce most graphs you might want and it has good set of functions for data analysis.  Inferential analysis in Excel is limited but there are add-in programs that expand the native functions.

What's the difference between PE_DataAnalysis1 and PE_DataAnalysis2? PE_DataAnalysis2 offers more flexibility in that you can choose the independent and dependent variables that are used in an analysis. (In PE_DataAnalysis1, the independent and dependent variables that are used in the analysis are preset. )  Also, PE_DataAnalysis2 uses Excel's pivot table function to produce the analysis.  This is a very powerful analysis tool that further enhances flexibility.  (You might want to read about pivot tables in the Excel Help pages so that you can enhance your ability to use the output from PE_DataAnalysis2.

How do I get a copy of PE_DataAnalysis2?   You can download PE_DataAnalysis2.xls using the Download  link on the PsychExperiments homepage. The download is fast because the file size is only 198Kb. The following icon will appear on your computer once the program is downloaded .

How do I get the data that I need? For the macro to be useful to you, you must have the experiment data on your computer.  Data are available via the same download page that is used to get the macro. Successful data downloads result in icons like those below in the directory where you choose to locate the data.  Note that there is a pe to the filename.  (If you wind up with any other extension--for example, Windows 2000 might insert a .txt extension if you don't choose the All Files option in the Save As window-- then just use the Rename function in the drop down menu that appears when you right click on a filename to eliminate the unwanted extension.).  The names for the datafiles you download are created automatically using the experiment name and the time of the download.   The pe extension indicates that the data are in the correct format for use with PE_DataAnalysis2.  The file format is a comma-separated text format. (i.e., commas are used as delimiters to separate the data fields).

What do I do once I click on PE_DataAnalysis2.xls? When you double-click the PE_DataAnalysis2 icon, one of two things will happen.  One is that you will get the following message:

 

 

 

This is a signal that you need to change the default security settings in Excel from High to Medium.  To do this, choose Tools/Macro/Security/Medium.  Don't worry.  Medium security is fine and it makes it possible to use our program.

With the Security setting at Medium, when you open PE_DataAnalysis2.xls, you will be warned that the file that you are opening contains macros. 

Choose "Enable macros."  (The file containing the macros is on a secure webserver, so you can trust us!  If you don't trust us then you'll just have to write your own program.)

After enabling the macros, you will see a standard Excel spreadsheet with one novel feature.  There will be a floating toolbar that looks like this

Click "Import Data."  You will be taken to WindowsExplorer so that you can browse to locate the folder that holds your downloaded data.  In this example, the data are in a folder labeled PEMacro.  The files with a pe extension will be displayed.

In the example, Mental Rotation data are selected.  Clicking Open loads the data into a single worksheet in an Excel workbook.  The worksheet label is ExpData.

When you click "OK," you are forced to save the Excel workbook with the automatically assigned name, though of course you could change the name to anything you like.  By saving the Excel file with a new name, the original PE_DataAnalysis2.xls file remains in its native state allowing you to use it to analyze other datasets. 

Clicking "Save" returns you to the Excel workbook that now bears a name other than PE_DataAnalysis2.xls (Here is is MentalRotation101655.xls.) Notice that the Analyze Data button is now active and the Import Data button is inactive.

   

Clicking "Analyze Data" takes you to a user interface in which you can select both the independent and dependent variables for a descriptive analysis.  The independent variables page, shown below, has an Independent Variables window which lists all of the column headings (except for ID) that are not followed by a # on the ExpData page.  Note the use of a ">" in front of some of the variables.  This symbol indicates a variable of primary interest in the experiment. The other variables are ones that are in the database for the experiment, but they may not produce an analysis of interest.  When in doubt about the variables you want to use, use those marked with a ">. "

In keeping with the principle of selecting variables preceded by a ">",  the variable Angle (i.e., the angle at which stimuli are rotated in the Mental Rotation experiment) has been selected for a one-way analysis.  The option of creating a table with this variable in Rows was selected by using the Set Primary Row button.

With the independent variable set as Angle, hitting the Next button (or clicking on the Dependent Variables tab) takes you to the page on which the dependent variable can be selected.

The dependent variables window displays the dependent variables that are available.  To select a variable, click on it, then use the Set Dependent Variable button.

Now clicking the Analyze button will produce a descriptive statistical analysis in which means, standard deviations, minimum and maximum values are computed for each value of the Angle variable.  A chart of means is also produced along with the standard errors of the means.

To look at the effects of other variables and the effects of variables in combination, click on Analyze Data again. Here's the setup for a two-way analysis using Trial Type and Angle as the variables.

This analysis produces the following result for the Reaction Time variable.

These examples show the performance of the VBA program that is included as a macro in the PE_DataAnalysis program, but it is important to recognize that all of Excel's analysis and editing functions are available to you if you know how to use them.  Here are two little Excel tricks that may prove useful.

  1. Change the format of the graph. Our VBA program produces a bar graph by default.  Often, however, a line graph will be a more appropriate way to dsiplay the effects that are present in a dataset.  To change the format in Excel, right click anywhere in the field of the graph to produce a menu with "Chart Type" as an option. Click on Chart Type to get a menu that allows you to change the type to Line.

Printing

        The new graph looks like this.   

           

  1. Using Controls on the Pivot Tables.  The tables on each datapage are pivot tables.  The rows and columns have controls that you can click and there is also a "Page Filter" control at the very top.  Ours is labeled ID because we used Participant IDs as the default Page Filter. When you click on it, you see options for filtering the data in the Pivot Table. Let's say, you just want to see the data for one of the particpants, AXE137.  Select that ID, then click OK.

 

 

 

 

 

 

        Now you will see the data for just the one participant.  Excel's entry #DIV/0 merely indicates that a dataset of N=1 has no standard deviation.

       

We hope that PE_DataAnalysi2.xls makes it possible for instructors and their students to actively explore data from PsychExperiments to determine which variables, whether alone or in combination with other variables, have effects and which do not.  Once the data have been explored descriptively, advanced students can go to perform significance tests on the presumptive effects.

1A macro is nothing more than a program, but the term macro tends to be used for programs that perform operations within application programs.  Our macro is a VBA (Visual Basic for Applications) program.