VBA Macros (Data Organization)

DISCLAIMER: All of my programs are free of charge. That being said, the user assumes all liability for the proper use and any complications pertaining to these files. I hope you can put them to good use!



Data Organization / Outlier Location Sheet: You can use this sheet for the organization and display of basic data sets. The Instructions tab indicates each of the individual features of the file, and the Template tab offers a blank structure. You can use this file to organize multiple between-subjects conditions, statistics of varying formats, etc.  It will allow you to quickly organize data, calculate descriptive statistics, and locate outliers.  Download the files here: Data Organization.



Outlier Location and Replacement Sheet: This sheet is particularly useful for large reaction time data sets. It can be fully modified for varying numbers of subjects, blocks, and trials. Indicate how outliers should be defined by specifying the number of standard deviations from the mean an outlier is defined as. Tables are automatically generated, simply paste in your data. Descriptive statistics are calculated, outliers are located (as are missing values), and with an additional macro, outliers are replaced with cutoff scores. Finally, statistics are displayed pertaining to the number of missing values and replaced outliers.  Download the files here: Outlier_Replacement.



Multidimensional Scaling Matrix Creation Sheet: These macros are useful for taking multidimensional scaling data obtained from E-Prime (or other sources) and turning it into individual subject matrices. It can be fully modified for varying numbers of stimuli and subjects. Simply copy your data into the supplied columns and use the macro button to create your matrices. For multiple subjects the matrices will be stacked and can be copied directly into statistical software packages (e.g., SPSS). NOTE: If you are using the JAVA version of the Spatial Arrangement Method (SpAM), you should use the macros posted directly below this paragraph.  Download the files here: MDS_MatrixCreation.



MDS Matrix Creation Sheet for the JAVA version of SpAM: These macros accomplish the same exact procedure as those listed directly above, with one exception. The output from the JAVA version of SpAM has some redundant values, whereas the output from any of my E-Prime files does not. This macro will account for the redundancies.  Download the files here: MDS_MatrixCreation_JAVA.



Multidimensional Scaling Matrix Concatentation Sheet: These macros are useful for taking multidimensional scaling data obtained from subjects who may or may not have provided enough ratings for full matrices, or for creating matrices with unequal observations. They will take the data obtained from MDS experiments and turn it into three matrices. The first, gives you the summed estimates for each pairwise comparison. The second, gives you the number of estimates obtained for each pairing. And the third gives you the averaged estimates. The macros can be fully modified to take varying numbers of stimuli and as much data as Excel can handle. Simply copy your data into the supplied columns and use the macro button to create your matrices. The averaged matrix can be copied directly into statistical software packages (e.g., SPSS). Finally, the macros have two noteworthy features: 1) if they detect a rating wherein an item is compared to itself, you will prompted to indicate whether or not you want that data included in your final matrices. And 2) any pair of stimuli that have not been rated will have an empty yellow cell in the averaged matrix, allowing a swift visual inspection for missing data points.  Download the files here: MDS_MatrixConcatenation.



Multidimensional Scaling (SpAM) Random Sampling Simulations:  These macros allow you to simulate a random sampling method for use with large sets of stimuli in the Spatial Arrangement Method (SpAM) of multidimensional scaling. If you have a large set of stimuli, it may not be possible to present all the items on screen at once. One way I suggest above is to employ a Steiner System to control stimulus presentation. Alternatively, one may choose to simply sample items at random, such that each subject sees each stimulus once. Such a design would not allow for a complete matrix to be filled by each subject, but with enough subjects, the overall stimulus matrix may be completed. These macros allow you to simulate different parameters for your experiment, to get an idea of how many subjects would be needed to fill your design. The user inputs the total number of stimuli, the number of items to be “displayed” (per trial), and the number of trials (per subject). You can also designate how many observations you desire per cell (use 1 if you simply want the matrix to be filled with a single observation per cell), and how many times you want the simulation to run. The output shows up in the sheet, by simulation number, giving the user the total number of “subjects” needed to fill the design on each simulation. For instance, the sample provided has 20 stimuli (so, a 20×20 matrix). The subjects are shown 5 items on each trial, and get 4 total trials. In order to fill this matrix with at least 2 observations per cell, it would take an average of 33.7 subjects. All of the parameters are modifiable, so play around with the sheet to get an idea of the number of subjects you’d need for your design!  Download the files here: MDS_RandomSampling.