What is a spreadsheet?

Spreadsheets were invented to make accounting easier, but all they really do is perform repetitive calculations quickly and accurately, so you can use a spreadsheet to keep your grades. Although it's not hard to record grades on paper, adding them up takes time, and if you go back and enter points for missing or excused work, you will need to recalculate the total each time. The spreadsheet does this automatically, saving you time and reducing errors. The spreadsheet can even convert your percentile grade into a letter grade. Now that's cool! Gradebook programs do the same thing, and with more bells and whistles, but they cost money and almost every computer already comes with a spreadsheet program. All spreadsheets work in pretty much the same way. We'll use Excel in this example.

Example:

Here's a sample spreadsheet that is being used as a gradebook...

Spreadsheet Terminology:

Spreadsheets consist of rows and columns of cells. Cells are the individual boxes. They can contain words or numbers. If a cell contains only numbers, the spreadsheet will recognize the content as numbers. If the cell contains anything other than numbers, the spreadsheet will ignore the content when performing calculations. A row is a group of cells running horizontally across the spreadsheet. A column is a group of cells running vertically down the spreadsheet. Rows are referred to using numbers and columns using letters, so the cell in the top left corner of the spreadsheet is A1, and the cell to the right of A1 is B1. The cell below A1 is A2. Get it?

Setting it up:

At first you may not see the advantage of a spreadsheet. After all, you still have to put all this information in it, and how is this any easier than on paper? Trust me. It will be. In the first row, enter the names of your assignments (or just call them Assignment 1, 2, 3, etc.) In the second row, enter the point value for that assignment. Down the first column, enter the names of your students. You may want to use the "lastname first" naming system since this will make your spreadsheet match the official school gradesheets and class rosters. You can create a separate spreadsheet for each of your classes or combine all students into one, but if you do put all students in one spreadsheet, be sure to include a column for class section so you will be able to sort by either class or lastname.

Calculations:

Now comes the fun part. (Well, at least this part is more fun than the rest.) In a spreadsheet, we use formulas to perform calculations. Here's a simple formula: =AVERAGE(B4:B8) What does this formula do? It takes the average of the values in cells from B4 to B8. You type the formula into a blank cell of the spreadsheet and, when you hit the Enter key, the answer is shown in this cell. In our example above, this is the formula in cell B10 and it gives the average score on Assignment 1. Here's another formula: =(B10/B2)*100 What does this one do? It divides the value in cell B10 by the value in cell B2 and then multiplies the answer by 100. The parentheses ensure that the order of operations is correct. In our example above, this is the formula in cell B11 and gives the percent score on Assignment 1. Here's a third example: =SUM(B4:H4) This one adds up the numbers from cell B4 to cell H4. In our example above, this is the formula in cell I4 and it gives you Fred's total points. How do we get Fred's percent grade? Use this formula: =(I4/I2)*100 This divides Fred's points by the total possible points and then multiplies his decimal score by 100 to convert it to a percentage. In our example above this formula is in cell J4. Now for the grand finale! How do we convert a percent score to a letter grade? Here's the formula: =IF(J4>89.4,"A",IF(J4>79.4,"B",IF(J4>69.4,"C",IF(J4>59.4,"D",IF(J4>0,"F"))))) Make any mistakes while entering this one and it won't work, so check your typing carefully. Note that this formula assigns no grade to someone who earned no points (maybe they are on the roster but never showed up), and that it rounds up for anyone who is 0.5 away from the grade above.

Copying and Pasting Formulas:

Typing formulas is easier than doing the calculations by hand, but it's still a lot of work, especially on that last one. Isn't there an easier way? I'm glad you asked. If you type a formula once, you can then copy and paste that formula to another cell and the spreadsheet will adjust the row and column numbers for you automatically. You have to be careful when doing this because the spreadsheet is guessing what you want (and it sometimes guesses wrong) so check your formulas carefully and make sure that the results they give you make sense. For example, if Fred gets more than 100 percent on his test, there's either a problem with the formula or his score has been entered incorrectly. If you want to copy a formula into a range of cells, select the cell containing the formula and choose Edit/Copy. Then select the range of cells where you want to paste the formula and choose Edit/Paste (or hit the Enter key).

Making a Chart:

Excel can also display your data graphically. Select the data you want to graph as shown here...

What's nice about the chart is that you don't need to rebuild it if you enter changes into the cells. The chart will update itself dynamically.

gradebook.xls