• by Charles A. Cavazos • IDRA Newsletter • August 1998 • 

The computer is a tool. But it is not as easy to learn to use as a rake. Let’s face it, no amount of honeyed words will get that rake to hop up and gather leaves into nice piles for you. But a computer will work for you with its own kind of magic.

This article outlines how to create a simple effective grade book using spreadsheet software. First, I have three quick disclaimers: I have chosen to use the spreadsheet in Microsoft Works 4.0 for Windows 95 for this article because it is a common software program available on many PC computers. (Microsoft Works is also available on Macintosh computers.) Second, this article is written for those who are not real comfortable with computers. If you find the pace too slow, skip ahead. Third, I am well aware that Microsoft Works already has a built-in grade book. And it sure looks pretty. But it is not user friendly. In fact, my computer came with a whole book on Microsoft Works, yet it does not tell me how to use the grade book! More importantly, by understanding how your grade book is created, you will understand everything there is to know about it.

For readers who are not sure what a spreadsheet is, let me describe one to you. It is like a sheet of ledger paper (on your computer screen) with well-lined rows and columns for numbers. It is capable of manipulating those numbers arithmetically, like calculating grade averages.

Before You Begin

Before you create a grade book, you have to consider the elements that will go into its design. Specifically, you must consider:

  • the period of time the grade book will cover,
  • the number of students,
  • the number of items to be graded, and
  • the way the final grade will be determined.

In this example, the period of time will be one six-week period, with 25 students, and 23 items to be graded (20 homework assignments and three tests). The homework grades will be averaged to make up one quarter of the final grade. The three test scores will each count as one quarter of the final grade. My grade book will be designed to figure all of this out. If you have Microsoft Works, follow along with me.

Start Microsoft Works

The first thing to do is to open the spreadsheet in Microsoft Works. To do this follow these steps (for Windows 95):

  1. From the desktop (the screen that remains in view after you have turned your system on) put the mouse pointer over the “Start” button on the lower left corner of the screen and click it. This will open the Start Menu.
  2. Move the pointer straight up to shade “Programs.” This will open the Program Menu.
  3. Look for “Microsoft Works” on the Program Menu. Move the pointer to the right and up or down until Microsoft Works is shaded.
  4. One more menu is opened; the Msworks menu. Click on this to open Microsoft Works.
  5. The Works Task Launcher screen will be displayed. Click on the folder tab that reads “Works Tools.”
  6. Four buttons are displayed. Click on the Spreadsheet button.

We’re In. Now What?

Take a look at the grid. The grid is the spreadsheet itself. The spreadsheet is made up of columns and rows. Notice that at the top of each column is a letter, beginning with A; to the left of each row is a number, beginning with 1.

The columns overlaid on the rows create the rectangles that you see. These column-row intersections are called cells. Cells hold numbers, text and formulas. Each cell has a unique address made up of the cell’s column letter, followed by its row number. For example, the address of the cell at the top left of the spreadsheet is A1. The cell directly beneath it is A2; to the immediate right of A1 is B1.

Look at cell A1. Its borders are shaded. This is because the cursor is currently in this cell, making it the “active” cell. If I type anything right now and press the enter key, it will be written into this cell. When I enter data I must take care to enter it into the correct cells. This will become evident as I design the grade book.

One more thing about the spreadsheet on the screen: you can only see a portion of it at one time. There are many more columns and rows available to you. I will need some of these and can scroll the screen to the right or down by clicking on the arrows at the bottom right corner of the spreadsheet. The cursor can also be moved by pressing the arrow keys on the keyboard.

Create the Grade Book

Now I will create the grade book. A grade book is used to record each grade for each student. Accordingly, I must distinguish the students from one another, as well as the graded items from one another. To do this I will let each row represent a student and each column represent an assignment or test. A row will hold all the data for one (and only one) student. A column will hold data for only one assignment or grade. A cell will then hold the item grade for the student whose row intersects the item column.

If I have 25 students I am going to need 25 rows; if I plan to administer 23 items to be graded (20 homework assignments and three tests) I am going to need 23 columns.

I will use two additional rows as column headings to tell me what I am scoring. I will also use three more columns: one to record the students’ names, one to average the homework grades and one to average the final grade.

 

A

 

B

1

HWORK1

2

Student

9/1/98

I will begin by moving the cursor and clicking on cell A2. I will type “Student.” (As you read this article, enter the text between the quote marks and not the quote marks themselves.) This is the column heading for column A. The only thing that will be entered in this column will be the names of the students. In cell B1, I will enter “HWORK1” (my abbreviation for “first homework assignment”). Immediately below that, in B2, I enter the date that the homework is assigned. I will type the first date as “9/1/98.” Accordingly, I will type “HWORK2” in cell C1 and its assignment date in C2.

I will continue to create my column headings across the spreadsheet until I write “HWORK20” in cell U1 and its assignment date in U2.

In column V, I want to average the homework scores. So in cell V1, I will type “HWORK” and in V2, “Average.”

The next three columns, W, X and Y, will contain the grades of three tests I will administer. In cell W1, I will type Test1, and its date in cell W2, and so on to cells Y1 and Y2.

Finally in column Z, I will average the 20 homework grades and three test grades for the final grade. I will type “Final” in cell Z1, and “Grade” in Z2.

 

U

 

V

 

 

W

 

 

X

 

 

Y

 

 

Z

 

HWORK20

HWORK

Test 1 Test 2 Test 3 Final

10/2/98

Average

9/15/98

9/25/98

10/6/98

Grade

To summarize where I am so far, column A will contain student names; columns B through U will contain homework grades; column V will contain a formula to average the homework grades; columns W, X and Y will contain test scores; and column Z will contain a formula to calculate the final grade. Since I used the top two rows for headings, the first row I can use for a student name is row 3. But for the sake of readability, I will skip row 3 and begin the names on row 4.

This is a good time to save the grade book. On the menu bar I will click File, then “Save As,” and enter a file name that adequately describes the grade book and the period it covers, “First Six Weeks Grade Book.” After I have named the file I can save changes at any time by clicking File, then “Save.” (If you cannot restrict access to your computer in the classroom, save the file to a diskette and keep it locked away. Keep a backup copy as well.)

The Formulas – No Problem!

Formulas are an integral part of the spreadsheet environment. If you make a mistake as you create a formula, you can use the “Undo” feature on the menu bar or close the document without saving your most recent changes and reopen it.

A formula is placed in the cell where you want the result to appear. On my grade book that means the formula that averages the homework grades for the first student will be placed in cell V4; for the second student, in cell V5, and so on to V28.

I have moved my pointer to cell V4. In it, I type “=AVG(B4:U4)” and press the enter key. Note that there are no spaces in the formula. Formulas have a syntax that allow them to be understood by the computer and the user. The meaning of “=AVG(B4:U4)” follows:

= means that what follows is a formula (as opposed to numbers or text).
AVG directs the computer to average what follows.
(B4 means begin with data in cell B4 (begin because B4 is the first cell listed in the formula).
: means “through.”
U4) means end with data in cell U4 (end because U4 is the second cell listed in the formula).

In sentence form it could be rendered as: “This is a formula. Average the valid entries in cells B4 through U4 and place the result in V4.”

Zero is a valid entry, a blank cell is not. If one of the 20 homework cells is left blank, the remaining 19 will be summed and divided by 19. If the same cell is filled with a zero, the 20 grades will be summed and divided by 20. Formulas are designed to adjust automatically as data are entered. This means that no matter where I am in the six week period I will always have an up-to-date average.

But on my screen right now, instead of showing “=AVG(B4:U4)” in cell V4, “ERR” is displayed. “ERR” means error. However, it is not really an error. “ERR” is displayed because there are no numbers yet in any of the cells in the formula (B4 through U4). Incidentally, this is the best time to check the formula. The first formula will serve as a model for the remaining ones. If the first formula is incorrect, the others will be incorrect as well. To check it I will move the pointer to B4 and enter 100. Cell V4 should now read 100. It does. Next I will enter 90 into cell C4. Cell V4 should now read 95. You can see how formulas behave interactively, working immediately as new data are entered.

 

W

 

 

X

 

 

Y

 

 

Z

 

Test1

Test 2 Test 3 Final

9/15/98

9/25/98

10/6/98

Grade

ERR

Take one more look at the formula. Notice that if the homework grades were not recorded in consecutive columns I could not use “:” (through) in my formula and would have to list the cell addresses individually. This is not difficult to do, but it is messier and more susceptible to error. Accuracy is the key; keep the design as simple as possible.

I will use the same formula for every cell in column V, but I must change the row number in each cell address to match the row that the cell is on. Otherwise William will have Mary’s homework average.

For example, the formula in cell V5 should be “=AVG(B5:U5)”; in cell V6, “=AVG(B6:U6),” and so on. The computer is not going to tell me if I am referencing the wrong cells. It will just calculate an incorrect grade average.

I will average the final grade the same way. In cell Z4, I will type “=AVG(V4:Y4).” Cell V4 will contain the homework average (for the first student), cell W4 the first test score, cell X4 the second test score, and cell Y4 the third test score. So the final score is composed of one quarter homework average, one quarter first test, one quarter second test, and one quarter third test. I will copy and adjust the formula into cells Z5 through Z28 as I did with the homework average.

Fine Tuning

There are two more design considerations. During the six-week period, I am going to enter grades in a matrix beginning in cell B4 and ending in cell Z28. That is a total of 650 cells. I already know that I can only see a portion of my grade book at one time. I can scroll down and to the right, but it will not be long before I scroll my column headings and student names right off the screen. “Let’s see, which column was I using for the 16th homework assignment? What row was Amanda on? Rats! What good are column headings if you have to memorize them?”

I don’t. I will place the pointer in the upper left cell that will contain scores and click on it, making it the active cell. In my example that is cell B4. On the top of the screen is the menu bar. I will click on Format. This will produce a dropdown menu where I will click “Freeze Titles.” This will freeze the headings above and to the left of the location of the active cell. This means that no matter how far I scroll down, the column headings will always be visible; no matter how far I scroll to the right, the student names will always be visible. This way I will not get lost.

The other design consideration concerns column A. This column is not wide enough to display most names without overlapping column B. And as soon as I enter a grade in column B, the name will be truncated. With his first homework score, “George Washington” will become “George Wa.” So, I will increase the column width by clicking on any cell in column A. Then I will move the pointer up to the menu bar, click on File, then click on “Column Width.” In the box that appears I will change the number from 10 to 20, and click the OK button. This is all it takes to change the column width. If the column is still not wide enough for some of the names, I can repeat the steps and change the 20 to something else.

 

A

 

 

B

 

 

C

 

 

D

 

 

E

 

 

V

 

 

W

 

 

X

 

 

Y

 

 

Z

 

 

1

 

 

HWORK1

 

HWORK2

 

 

HWORK3

 

 

HWORK4

 

 

HWORK

 

 

Test 1

 

 

Test 2

 

 

Test 3

 

Final

 

 

2

 

Student

 

9/1/98

 

9/92/98

 

 

9/3/98

 

 

9/4/98

 

 

Average

 

 

9/15/98

 

 

9/25/98

 

 

10/6/98

 

Grade

 

3

 

 

4

 

George Washington

95

88

98

88

94

88

91

 

5

 

Student name

88

95

88

95

90

 

92

91

 

6

 

Student name

92

88

95

88

92

78

85

 

7

 

Student name

78

92

88

92

83

93

88

 

8

 

Student name

93

78

92

78

90

87

89

 

9

 

Student name

87

93

78

93

88

77

82

10

 

Student name

77

87

93

87

82

84

83

 

11

 

Student name

84

77

87

77

83

91

87

Now that the design is complete, all that remains to do is to enter the names and record the grades (in the correct cells, of course!). I will save these latest changes.

When my matrix is full at the end of the six weeks, my final grades will be calculated for me. I can change a grade at any time and be confident that the final grade will accurately reflect it.

There are many more things I can do to make my grade book better – insert rows and columns, round figures, shade rows, etc. But for now the bare bones design is enough to get anyone started. Look around at the other features sometime. Explore the menu bar. Try using the Help menu. Just remember to save often.


Charles Cavazos is a computer specialist in the IDRA Division of Research and Evaluation. Comments and questions may be sent to him via e-mail at feedback@idra.org.


[©1998, IDRA. This article originally appeared in the August 1998 IDRA Newsletter by the Intercultural Development Research Association. Permission to reproduce this article is granted provided the article is reprinted in its entirety and proper credit is given to IDRA and the author.]

Share