Tag Archives: teaching

Any instructor that spends any amount of time fighting with a grade-book can vouch for the truth that any method to save time when dealing with simple repetitive tasks is worth its weight in gold. Even something that costs you as little as 5 seconds per student per assignment can add up quickly. For example, if you take a course like the one I’m currently working on at ASU. A course of 48 students with 8 major assignments that require a rubric-like report. Copying and pasting may be a quick solution when moving back and forth between the LMS (Learning Management System) and a spreadsheet, but at a transaction cost of 5 seconds that’s 96 minutes over the course of the semester.

What then can a person do to save some time? This particular solution lies in the ability of spreadsheet applications like Google Spreadsheets and Excel to combine cell values using the “&” command and the carriage return character when writing formulas.

To make this work I started by looking at an existing grade-book and rubric for some established assignments. In this case I’m looking to write a formula that will take the values entered in the cells of this rubric and generate a report for each student that looks something like:

In the example above, any italicized letters are values that are specific to each individual student and each assignment. In other words, I want the formula to package all of the relevant information into a single cell that I can easily copy and paste into a text window in the LMS.

To do this I need to know a few things about Excel / Google Spreadsheets. In these applications there are two methods of referencing cells that are important to keep in mind: relative references and absolute references.

Relative References are values that are expected to change. When you you copy and paste a formula that contains a relative reference, the reference changes in relation to where it’s pasted. For example let’s say that you write a simple formula like this:=A1

This tells the spreadsheet application that you want to display the contents of cell A1 in your target cell. If you were to copy this formula to the cell directly below your original target cell, the formula would change to:=A2

This is because your spreadsheet assumes that you want to maintain vertical continuity with the data in your cells.

Absolute References are values that you want to freeze at a particular location. When you write an absolute reference you’re telling your spreadsheet that no matter where the formula is moved to, it should always reference the same cell. This can be especially handy if there are values that you know you want to point to in a formula that need to stay fixed. When creating absolute formulas you use the “$” symbol to specify which value you want to freeze (you can freeze just the row, the column, or both). In my case this looks like:=&A&1

The other operator that we need to think about using when using our spreadsheet application is the “&” operator. This will allow us to pull in values from multiple cells and place them in the order we specify.

I started my formula the following way:

=”Graded by ” &J55&CHAR(133)CHAR(133)

This formula generates the line “Graded by Matt” followed by two carriage returns (CHAR(133) inserts a return in a cell). The next portion of the formula looks like:

&$D$2&” – “&D4&CHAR(133)

This line generates the line “States Interests and Goals – points awarded” followed by a carriage return.

If you take this same principle and repeat it for each column you end up with something like this: