Rather than tackling a big issue in education, I decided use this Hub to share a tool that I have used for years in my capacity as an adjunct or part-time economics instructor at a local Community College. Holding down two jobs requires a large time commitment and if I am going to do a good job teaching and still have time to relax with my family it is important that I be efficient. The area which provides the greatest opportunities for finding efficiencies is the out of classroom paperwork of which grading is one of them.

As a teaching assistant while in graduate school in the days before personal computers, I had the experience of recording grades in a paper grade book with a pen and then using a calculator to total each student's grades convert them to a percent and record the corresponding letter grade.

However, the advent of the electronic spreadsheet changed all that and when I began teaching part-time I decided that the job of tracking and calculating grades could be done faster and more accurately on a spreadsheet. I have used a variety of spreadsheet programs over the years, beginning with the old Lotus 1-2-3 for DOS and all of these work the same with only a slight variation in the way the calculations are written.

In recent years I have found the Spreadsheet in Google Docs to be the best in terms of cost (it is free) and versatility - it is on the Internet and thus accessible 24/7 from any computer with Internet access. As a part-time instructor I don't have an office at school and this having the grade book available in my Google Account on the Internet allows me to access it from home, school or work at my day job.

To get started you first need a Google account with the Google Docs feature.

This is free and can be obtained by going to Google.com, clicking on Sign In in the upper right and then clicking on Create and Account Now link. You do not have to use a Gmail address for your sign in as you can use any email address as your user sign-in.

Once you have created your account, simply click on More which appears in the upper left next to various Google services such as Gmail, Shopping, etc. and then click on Documents in the drop down box. This will add Documents to the Google services available on your account and from this point on simply click on My Account and select Docs to go into your documents and spreadsheets (spreadsheets are a part of documents).

To create a new document or spreadsheet, simply click on the New button at the top and then click on Spreadsheet in the drop down box and a blank spreadsheet will appear. It is a good idea to click the Save button at the top right and give the spreadsheet a name - once named, the spreadsheet will automatically save as you work in it.

For this example I created a simple spreadsheet with columns for five assignments and tests, one column for a student count, one for student names and three for grade calculation. Refer to the photo below for illustration. The instructions will continue below the photo.

- STEP 1 - CREATE A TITLE LINE AT THE TOP: Place the mouse pointer in cell A1, click and hold the left mouse button and drag the pointer across the first row of cells to cell J1. Release the mouse button and the first ten cells at the top should be highlighted. Next, point to the merge button (see Example 1 below) and left click once. This will cause the ten cells to merge into a single large cell.

Repeat the Process in row 3. You now have two lines for a title and subtitle. You can center your titles by clicking on the cell in which you want to center the text and then click on the down arrow next to the Alignment button which is immediately to the left of the Merge button on the Formatting Bar and then click on the Centering Button.

To bold the title, move further left on the bar and click on the black B button, and to change the font size go one more button to the left and click on the down arrow in the Font Size button (which reads 10 pt in the example) and click on the desired font size.

- STEP 2 - ADD COLUMN HEADINGS IN ROW 7: In Row 7 I entered my column headings in the ten cells. You can bold and center these within the cells using the same process. 

           INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW

- STEP 3 - CREATE CALCULATIONS TO SUM STUDENT SCORES: In Row 8 enter the maximum point score beneith each test and assignment.

Click on cell C8, click on the Sum Button (last button on right on Button Bar in photo below), select Sum, highlight cells F8 through J8, place a right parenthesis ")" after the J8 in cell C8 and hit ENTER. This gives the total points for the course.

Leaving the cursor in cell C8, click Ctrl C to copy the contents of cell C8 (you can also right click your mouse and select copy).

Highlight cells C10 to C19 and click Ctrl V (you can also right click your mouse and select paste) to paste the formula in cell C8 into these cells. Of course, instead of summing cells F8 through J8, the formula in each cell will adjust to sum the cells in columns F through J in its own row.

INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW

- STEP 4 - CALCULATE PERCENT SCORE FOR EACH STUDENT - In Cell D10 type the formula:    =C10/$C$8   (see photo below)  which tells the computer to divide the number in Cell C to its left by the number in cell C8. Using the dollar signs ($) in front of the C and the 8 causes the computer to keep this cell constant in the formula and not adjust for each new row, as we want to divide a student's total points which are in the cell in Column C immediately to the left of the calculation by the constant number that is in cell C8.

INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW

- STEP 5 - DISPLAY DECIMALS IN COLUMN D AS PERCENT - to change the display of the numbers in Column D from decimal to percent place the mouse pointer in Cell D10, click and hold the left mouse button and drag the pointer to Cell D19. This will highlight the column.

Next, place the mouse pointer on the down arrow to the left of the button labeled 123 on the Button Bar above (this button is located just below the word Format in the Title Bar).and click once to get the drop down box (see photo 2 below). Click on the 10.12% to select the un-rounded percent option.

By choosing the un-rounded percent display rather than the rounded display option above it you will cause the computer to display the student's exact percent score which is what the computer will use in calculating the letter grade in the next column. This becomes critical when the difference in a student's score is one half of one percent or less than the percent grade needed for the next higher letter grade.

For instance, if you choose the rounded percent display a student with a percent grade of 79.6% will display as 80% in Column D but Column E will display a letter grade of C because the computer will base the letter grade on the exact percent score and not the rounded score.

(NOTE: if you want to use the rounded score and have the letter grade match that score you can accomplish this by lowering the threshold for each letter grade by a half a point making an A equal to 89.5% or greater rather than 90% or greater, a B equal to 79.5% or greater rather than 80% or greater, etc.)

INSTRUCTIONS CONTINUE AFTER THE PHOTO BELOW

- STEP 6 - CALCULATE STUDENT'S LETTER GRADE IN COLUMN E - (see photo below) Place the mouse pointer in Cell E10 and click and enter the following formula in Cell E10. You have three options for doing this:

1 Click on the down arrow on the Sum button on the Button Bar above and select the IF statement and build the formula using the layout that comes with the function.

2 Type the formula below into Cell E10 (NOTE: for the formula as it exists below to work properly your spreadsheet MUST be set up EXACTLY like my example cell for cell since the formula below references specific cells in the spreadsheet).

3 Highlight the formula below on your screen, key Ctrl C (or right click your mouse and select Copy from the drop down menu), go to your spreadsheet, point to Cell E10 and key Ctrl V (or right click and select Paste from the drop down menu) to paste it into Cell E10 on your spreadsheet (this worked on a Google spreadsheet when I tested it - I had previously set up the spreadsheet exactly like the one I have displayed in the example lacking only the formula in cell E10).

=if(D10>=.90, " A",if(D10>=.82," B",if(D10>=.74, " C",if(D10>=.65," D"," F"))))

Let me explain how the formula above has been constructed. The IF statement in all spreadsheets is basically logical TRUE/FALSE test in which we have the computer evaluate a statement and provide one answer if the statement matches our definition of True and a different answer if the statement is not True. In the grading case we want the computer to evaluate and select one of five rather than one of two options. We do this by having it first test for an A and return an A if the percent score is equal to or greater than the lowest numeric score for an A and, if the statement turns out not to be true (i.e., the numeric score is less than the threshold amount for an A) to come back and reevaluate the number to see if it meets the test for a B and so on until we reach the D score where it comes back with a D if true and an F if not true (i.e., the threshold score is less than that needed for a D).

In doing this it is imperative that we not only have the same number of right parenthesis ")" as left parenthesis "(" but also that each one be in the correct position.

Second it is imperative that we separate the end of the test, the true statement and the false statement each with a coma.

Third if we are using letters or alpha characters in out test or our answers that we identify them by putting double quotation marks on either side of them. Further, there can be no blank spaces in the formula and if we want a blank space (as I have done by placing one of them before each letter grade) that it be treated as an alpha character and set off with double quotation marks.

Finally, we have to use one of the following sign combinations for our evaluations:

= equal to

> greater than

< less than

<> not equal to

>= greater than or equal to

<= less than or equal to

When using these characters we must be very specific. Thus if we write that an A is =90 then only scores of 90 will receive an A, any other score, including 100 will NOT be an A. If we write that A is >90 then every number greater than, but NOT INCLUDING 90 is an A (90.1 is an A while 90 is not)

This formula is placed in Cell E10 and looks to the number found in the cell immediately to its left. Even though we have instructed to display the student's score in Cell D10 as a percent, the computer will still read it as a decimal so all of the numbers we write in the formula must be written as a decimal - thus an A is >=.90, NOT >=90% and NOT >=90.

Once you have completed writing and checking the formula in Cell E10 hit ENTER to save it in the cell.

Then place the mouse pointer in Cell E10, key Ctrl C to copy the cell, move the pointer to cell E11, click and hold the left mouse button and drag the pointer to Cell E19, release the left mouse button and key Ctrl V to paste the formula in each of the cells from E11 to E19. The computer will automatically change the cell reference number where it looks for the numeric score from D10 in the original formula in Cell E10 to the number of the cell immediately to the left of each of the cells in column E in which it was pasted (thus the formula in Cell E15 will reference cell D15).

The grade book is now complete and should look like the sample below except that yours will have your students' names and scores.

You can add scores for additional tests and assignments in the columns to the right - just be sure to adjust your formulas in Column C to include cell number of the last column you use (also don't forget to do the same for the total possible points in Cell C8 as well as to add the maximum score for each new test or assignment in row 8 of the column beneath the test or assignment name).

Since I teach college courses, I set up my spreadsheet with just one final score for each student in each course I teach. If you teach K-12 you may want to create a spreadsheet for each subject or class for each grading period.

Because it can take some time to create this spreadsheet, I have made a practice of re-using the same sheet by copying it. This can be done in one of two ways, either take the spreadsheet from the class just completed and save it under a new name and then deleting the students' names and scores (but leaving the headings and calculations in place) or create the sheet and rather than using it save it under a new name using the new sheet for the current class and saving the original as a template to make copies from in the future.

Finally, for back up you can export or download the spreadsheet from Google to your home and/or office computer. The downloaded copy can be in the form of a MS Excel file, an OpenOffice Spreadsheet file, a CSV file format or PDF format. When exported in an Excel or OpenOffice format you can continue to use it off line as a spreadsheet (however, it will not automatically update the online copy - however, you can always delete the online copy that hasn't been updated and then upload the current file from your desktop or laptop to Google Docs and continue working online).