SWK 3402.3-3.2 Exercise in Transforming Variables

INTRODUCTION

The purpose of this activity is to remember how a new variable might be created in a data file. This is commonly necessary when administrative data are used to create evaluation variables and when complex measures are used, needing to be simplified into fewer variables than the number of items on the original measure. In this example, we will take the first 8 questions on the post-only evaluation of the coursebook for our prior research class and combine them into a single overall satisfaction score for the book. NOTE: This is not necessarily a good idea because we do not have validity data confirming that these items belong together in a single variable, but it serves the purpose of remembering how to transform and create new variables. We will be combining 8 ordinal ranking values (that 1-5 scale) into a single scale variable in this exercise–the mean of rankings assigned to the items by each individual student.

INSTRUCTIONS

1. Open the data file called “sections merged 1 to 7” (this is a combined file with scanned data from 7 of the class sections—the other 3 sections came in too late to be included in this exercise but will be included in the project final report).
2. Because the comments column (currently K) is so wide, it will be easier to view the data if we add a new variable BEFORE that string (alphanumeric) variable. This is accomplished by first clicking on the column header K. You should see the whole column highlighted as a result.
3. Then right click (in PCs) or use CONTROL (in Macs) on the highlighted column to insert a column before the current K column—follow the drop down menu options. You should now see an empty column K and the string variable about comments and observations became column L.
4. Next you need to name your new variable something meaningful that meets the naming requirements in Excel. Something like overall_satisfaction might work. This name will go in the cell K1.
5. Transforming the 8 values in columns B through I involves computing the mean/average of those scores for each student participant. Start by clicking on the empty cell K2. With that cell highlighted, go up to the ∑AutoSum dropdown menu on the far right side of the top menu bar. Select the option “average” and edit the identity of the cells in the range to be included—B2 to I2 (capital letter “I” and 2, not the number 12). We do not want to accidentally include the class number or the Question 9 data in this average/mean calculation.
6. Next, copy the cell K2 (it should have the number 5 showing in it, but you are actually copying the formula behind that number 5) and paste it in the rest of the column’s cells—K3 to K122. At this point, you should have different numbers in each of the K column cells and if you double click any of them you should see the formula for that row identified. For example, double click on cell K5 and it should say =AVERAGE(B5:I5) where I5 is the capital letter “I” and number 5 (not the number 15).
7. Let’s go one step further and review how to compute descriptive statistics for this new variable. Pick an empty cell, preferably at the end of the K column just for the sake of keeping things organized—maybe K124 for example. Highlight that cell, go up to the same ∑AutoSum dropdown menu on the far right side of the top menu bar and again select the “average” option. This may need to be slightly edited to include the cell range K2 to K122 and should read =AVERAGE(K2:K122) to accomplish this aim. You might wish to label the result in the cell J124. The value should be 4.
8. But, we might be a tad suspicious of this nice round number when we are computing a mean—there do not seem to be any decimal places in this result. Right click on the cell (in PCs) or CONTROL (in Macs) and select the “format cells” option. You will see that the default is 0 decimal places. Change this to 2 and the result changes to a mean (average) overall value of 4.01 which is more realistic for a computed mean.
9. In short, the 122 students for whom data were available described the free, online, interactive coursebook for our first semester course as “somewhat better” than traditional textbooks. If your results look similar to the file called “sections merged 1 to 7 overall rating finish” then you have successfully completed this exercise. Congratulations!