Combining Multiple Comments into a Single Grade Item
The TSQS optical scoring data provided by IST may include a number of columns in addition to the raw score including the students' wrong answers and their actual responses.
eClass Moodle will only allow instructors to import one column as the 'Comments' associated with a single grade item so for many instructors, this means that they need to decide which information students get access to, and which they don’t. The instructions below will detail how to combine the contents of two cells in your spreadsheet data into one so that students can have access to both sets of information in the 'Comments' field for a column.
- First open your exam data spreadsheet in Excel. It will likely resemble the following:
- Add a new column to your spreadsheet, and designate a name for that column in row 1:
- Make note of which columns contain the information that you would like to make available for your students. In this example below, we wish to have both the 'Items Wrong' and 'Scored Responses' items available for students, so we make note of F:F (the column designation for 'Items Wrong') and G:G (the column designation for 'Scored Responses').
- Select (click on) the first empty cell in the column you just created (in row 2), and in the formula bar enter the following:
="title1: " &x1:x1 &" ; "& "title2: "&y2:y2, where:
- title1 is replaced with the title of the first column you identified as one to make available to students (so in our example, 'Items Wrong')
- x1:x1 is replaced with Excel’s column designation that corresponds to your 'title1' column (so in our example, 'Items Wrong' corresponds to the column designation 'F:F')
- title2 is replaced with the title of the second column you identified as one to make available to students (so in our example, 'Scored Responses')
- y2:y2 is replaced with Excel’s column designation that corresponds to your 'title2' column (in our example, 'Scored Responses' corresponds to the column designation 'G:G')
- Note: if you see no Formula Bar, you can create one by selecting 'Insert' and clicking on Function.
After pressing Enter, you will see that the the contents from each specified column in row 2 have been combined into the cell you selected. In our example, the contents of cells F2 and G2 have been combined into the cell we had selected, E2, along with the additional descriptors we added to the formula (title1 and title 2).
- In a class of many students, you will not want to retype this formula for every student, so next we will copy the formula to the rest of our new column. To do this select (click on) the newly formatted cell, then hover over the bottom right corner of the cell until the cursor turns to a 'Plus' sign.
- Next click down and drag the cursor down through all the cells in the column and release. The formula should be repeated for all the rows in the column.
- Save the document - it is now ready to be imported into the Moodle gradebook. When you map your fields, you can now to choose to map the combined column as comments for the associated grade item. For more information on importing the grades into eClass Moodle, please view the following article: Importing Grades into the Grader Report.