E-MAIL YOUR GRADE SHEETS TO STUDENTS

 

By Rich Cameron Cerritos College
November 1999

By far, the worst part of teaching has got to be grading. Unless you issue all A's these days, someone is going to be unhappy. One way to stave off the complaints is to make sure students are often reminded how they are doing in the class and what scores you are using on which to base a grade.

But I disdain the old method of posting grades on the wall, where students can compare their grades with other anonymous students in the class. Even with student ID numbers, unless you do some kind of sorting, students can determine who is getting what grade. And I don't like posting EVERY score on EVERY assignment for EVERY student to see. I prefer, instead, to give students individual grade reports, with just their scores and evaluations. Students should be comparing themselves against themselves, not other students. But printing out individual grade reports on a regular basis is a terrible waste of paper and time. Now that most students have e-mail, sending out individual grades reports electronically is an outstanding alternative. And with proper use of a spreadsheet program, such as Microsoft Excel, sending weekly, individual grades reports can be made easy.

Of course, your students must have e-mail for this to work. And students must be encouraged to read their e-mail. At Cerritos College, like at many colleges, students are eligible for a campus e-mail address when they enroll: They just have to activate it. So in each of my classes the first assignment of the semester is to activate their address and send me a short e-mail message with their name and which class they are in. Those with external addresses can forward their campus e-mail to them and I don't have to worry about keeping track of addresses when people fail to pay their bills or switch Internet providers.

From these messages I build a database for each class of e-mail addresses. Even in today's electronic information age, this often is the first Internet experience for many students, so it is important to respond to the messages, even if your response is to say, "I got your assignment."

To encourage use of e-mail I send copies of relevant articles and weekly reports of what we'll be doing in class. Students can pass the class without using e-mail, but I try to enhance the course experience for those who DO read their mail.

BUILDING GRADE REPORTS

Once you've got students using e-mail, you still face the challenge of creating individual grade reports to send out. Creating an Excel grade sheet to do this for you takes a bit of up-front work, but once built can be used from semester to semester. The complexity of your grading system and your ability to use spreadsheets will dictate how informative your grade reports are, but this article will show you how to build a relatively simple grade sheet. You can enhance them from there.

Grade sheets should perform at least three functions: You must have an easy way to record grades, a way to view the summary grades for the entire class, and a way to generate individual reports automatically. Spreadsheet programs that allow multiple-page worksheets, such as Excel, make this relatively easy: You create a page to enter scores and have the summary pages and the individual pages pull information from that page to create your report.

Let's start with five students with five assignments each. Because this example is simple, the enter data function and class summary can both exist on the same page. If you have a variety of score generators, such as tests, homework, papers, and projects, you might find it easier to create multiple enter data sheets, one for each type of assignment. In such a case, you would want to create a separate class summary sheet. See Figure 1.

FIGURE ONE : THE SCORE SHEET

The scores in Row Three, Columns E through I represent total points possible for each assignment. Enter them only as assignments are graded and recorded. The number in Cell B3 represents the total points possible for the five assignments The formula is:

=sum(e3:i3)

I've found it particularly useful to name cells that contain totals like this. For instance, in this example I have named B3 as "Ttl_points". E3 is "assign1", F3 is "assign2", G3 is "assign3", etc. This makes calculations easier to understand months from now and also reduces the chance of errors. Use the INSERT-NAME-DEFINE option to create names. It is also wise to rename the sheet. While it is not needed yet, once you create multiple pages you'll want help in figuring out what each one represents. To rename the sheet use FORMAT-SHEET-RENAME.

Column B shows each student's totals. The formula resembles the one used to total the points possible.

Column C shows the each student's percentages. If you've named B3, then the formula for C5 would simply be:

=b5/Ttl_points

Column D shows each student's letter grade. It is nice to have Excel determine this for you, too. The formula makes use of if-then statements and looks like this:

=if(C5>0.899,"A",if(C5>0.799,"B",if(C5>0.699,"C",if(C5>0.649,"D", "F"))))

Note, there are no empty spaces in this calculation. Unfortunately, the calculations needed to create + and - grades are too many to work in a single simple formula.

STUDENT REPORTS

Next create the student reports. Now, this can be time-consuming if done improperly. After all, you need to create a page for each student in your class. The trick is to create one page correctly so that you can simply duplicate it and make minimal changes.

Go to Sheet Two (see tabs at the bottom of the worksheet). If there are no extra tabs at the bottom add a sheet by selecting INSERT-WORKSHEET. Let's rename the page right away. I'm going to rename it as "5." The reason for this is will become apparent soon. To rename the worksheet select FORMAT-WORKSHEET-NAME.

The first thing we'll want on the page is the student's name. Rather than type in the name, we want Excel to do it for us by using a calculation. We want Excel to pull the name from the "Enter Scores" page. There is a simple way to do this. Click in cell A1 and type an equal sign (the beginning of a calculation). Now click on the tab at the bottom of the page that takes us back to "Enter Scores." Click on cell A5 where John's name is. Then click on the green check mark in the formula editing area at the top of the screen. The following calculation is created:

='Enter Scores'!A5

and John's name now appears in cell A1 of page 5. Here is where we start to make little changes that are going to make recreation of our pages easier later. Add dollar signs in front of the A and the 5, thusly:

='Enter Scores'!$A$5

More on why we do this later.

Next we create a layout to report the scores we have for student John. I've found that vertical layouts work better than horizontal layouts for our ultimate goal of e-mailing grade sheets. I've also found that students want the bottom line first, so I report the semester percentage and the letter grade first, then the individual scores. See Figure 2.

FIGURE TWO. THE STUDENT REPORT

The percentage and letter grade are pulled from the "Enter Scores" page the same way we got John's name. Likewise, the dollar signs have to be inserted into the formulas:

B3 ='Enter Scores'!$C$5

C3 ='Enter Scores'!$D$5

Assignment names are drawn from the "Enter Scores" page in like fashion, EXCEPT that we do not want to add the dollar signs. Anything that is drawn from the "Enter Scores" page that will ultimately be identical on every student report does not use the dollar signs. Anything that has to be unique to a student does. So draw the actual scores for the assignments from the "Enter Scores" page, but DO add the dollar signs for cells C5 through C9.

Creating a student report can be that simple. But let's give the student more info. Students may not know, for instance, the significance of the scores if they don't remember how many points were possible on each assignment. We can use a calculation to remind the students. Figure 3 reminds the students what was possible.

FIGURE THREE. ADDING POSSIBLE SCORES

Excel uses the ampersand to tie together two or more pieces of information in a calculation, in this case contents from two different cells and a piece of text. In addition, a slash has been added to separate the two scores. The formulas for cells C5 through C9 would now look like this:

='Enter Scores'!$E$5&"/"&assign1

The slash is added with &"/" and the possible score is added with &assign1. Remember, "assign1" is the name we gave to a cell on the "Enter Scores" page (cell E3). No dollar signs are needed with our additional material because the information will be the same on all student pages.

We can help the student understand the significance of the score by creating a calculation to show the percentage that 22 points out of 25 represents. We'll do this in cells D5 through D9 with the following formulas:

D5 ='Enter Scores'!$E$5/assign1

D6 ='Enter Scores'!$F$5/assign2

D7 ='Enter Scores'!$G$5/assign3

D8 ='Enter Scores'!$H$5/assign4

D9 ='Enter Scores'!$I$5/assign5

Note that the dollar signs must be added to the E5, F5, G5, H5, I5 (See Figure 4). The numbers in the cells have been formatted to show up as percentages rather than decimals. You can customize the appearance of numbers by choosing FORMAT-CELLS-NUMBERS and choosing an appropriate percentage format.

FIGURE FOUR: ADDING PERCENTAGES

Our student report is done and ready for e-mailing. Whenever you update the "Enter Scores" page this page will automatically be updated. Before we learn how to e-mail it to students, we need to create pages for other students in the class.

DUPLICATING THE PAGE

Putting in all of those dollar signs was a bother, but now is where it pays off. You may have noticed that we had a lot of $5s on the page, and that "5" was the name we gave to the page. That's because all of the unique information for John was on row 5 of the "Enter Scores" page. All of the unique information on Mary, our next student, is on row 6. All we have to do is duplicate John's page and change all the $5s to $6s to create her page.

Select the entire page for copying (EDIT-SELECT ALL) and copy it (EDIT-COPY). Click on the tab at the bottom of the worksheet for Sheet Three (or add a new sheet if necessary). Click in Cell A1 and paste the clipboard onto the page (EDIT-PASTE). Let's let the program make the changes for us. Choose EDIT-REPLACE.... In the "Find What" field type in $5. In the "Replace With" field type $6. (See Figure 5). Click on the "Replace All" option and, viola, Mary has her own page. Now, rename the page so that her page is easy to find.

FIGURE FIVE: CREATING A SECOND STUDENT REPORT

Duplicate these steps to create pages for as many students as you have in a typical class. If you are creating this worksheet before the start of a semester, you may want to simply type in "Student 1," "Student 2," "Student 3," etc. for now. Note: While it would be nice to have the student's name appear on the tab at the bottom of the page, there are two problems with this:

First, there is no way to automatically name a page based on the contents of a cell, or at least I haven't found one. You would have to type in the name manually, which would be time consuming and would require changes to every page every semester.

Second, by the time you add 20-50 pages to your worksheet you'll find long names, even if only four or five characters, will spread out too much. Numbers are short and easy to locate by paying attention to the rows that contain a student's unique information.

E-MAILING THE GRADE SHEET

We're ready to mail the grade sheet. You could, of course, send grade reports as an attachment to an e-mail message, but that would require the student to have a spreadsheet program to read the attachment. What we've created can be copied and pasted into a message itself.

You've got to have both your spreadsheet open and your e-mail program open. Any e-mail program that allows copy and paste will work. I imagine there must be one to two out there that don't allow copy and paste, but this is a pretty basic function for computers and most will allow it. Figure 6 uses an America Online window.

To mail the message, first copy the individual report. (Select the area to be sent. In our example it would be cells A1 through D9. Then choose EDIT-COPY.)

Switch to the e-mail program and address the message to the student. You may want to create a column on the "Enter Scores" page to record a student's e-mail address and then create a calculation on the student's page to pull the address, say to cell A3. That way the student's address is right in front of you. Many e-mail programs also have built-in address books. A problem with this, though, is that after each semester you either have to clean out your address book or you'll end up with a lot of out-of-date, unneeded addresses.

You can even timestamp your reports by including the following formula into a cell on the report page

=NOW()

and using the FORMAT-CELLS-NUMBER-DATE option to format it.

Paste the report into the message window.

FIGURE SIX: E-MAILING THE REPORT

Repeat the above steps for each student. With a little practice, a class of 30-40 can be done in less than 15 minutes.