Gradebook Basics

Gradebook Basics

Gradebook basics

Below you see an image of the beginning of a gradebook. Several elements of this image will be discussed one at a time.

This is not shown so that you can copy my information into a spreadsheet. It is Only shown as an example.
As you experiment with using an Excel workbook as a gradebook, you should keep it simple.
Start with 4-5 names and at most two types of grades.

Legend

Legend - List each item for which you gave a grade. Be specific, you have plenty of room and later you may wish that you had recorded more information about what assignment the grade was given for.

Grade Policy

Grade Policy - You should clearly spell out how you will use grades to determine a student's final grade. An Excel worksheet provides enough room that this information could be included on each gradebook page.

Function

Equation for determining grades (Function) - The equation above applies my stated grade policy. If you can state your grade policy as an equation, you can write an Excel function to do the calculation. The function above does the following

Test grades
- Add the three grades (D7+E7+F7)
- Divide by the number of grades (average)
- Multiply by 0.4 (40 %)
Project grades
- Add the two grades (G7+H7)
- Divide by the number of grades (average)
- Multiply by 0.5 (50 %)
Homework grades
- Add the four grades (I7+J7+K7+L7)
- Divide by the number of grades (average)
- Multiply by 0.1 (10 %)

Filling the function into other cells - In the sample worksheet above the function has been entered into cell M7. Click on the bottom right corner of the cell and drag down to the last cell where the function is needed. In the example above that would be cell M17.

Formatting data

Averages can be displayed to whatever precision you wish to use. I used one decimal place, although you may wish to use zero decimal places. Zero decimal places would keep the grades in a format like they are reported to students. An advantage of using zero decimal places would be to avoid confusion regarding rounding grades. To illustrate this consider the following grade:

A grade of 75.49 would round to 75 with zero decimal places. However, at one decimal place that grade rounds to 75.5 and students would have the expectation that the grade would round to 76. Using zero decimal places will allow Excel to round without confusion to some students.

How to format

1. Highlight the column to be formatted by clicking on the letter at the top of the column.

2. From the Format menu choose Cells

3. From the Format Cells window choose Number and then select the number of decimal places you want to use.

Advanced gradebook topics

Using a Lookup table

Now we will ask Excel to look at the numerical average in column M and compare it to a list which defines the grading scale, for the purpose of assigning a letter grade to the average. Room was left at the top of the gradebook for this purpose.

The information to the left, defining the grade scale must be entered in ascending order from top to bottom. The number entered to the left of a letter must be the lowest number grade that would equal that letter grade.

enter the numbers and letters
highlight the entire range from A1 to B5
Go to the Insert menu, select Name and choose Define
Give a name to this lookup table, I called mine grades

Next we will write an equation which will look at a student's numerical average, look at a list of grades, and assign a letter grade to the student. This is done with a functioned named VLOOKUP. The equation must specify three elements:

The location of the numerical grade to be compared (M7 in the example)

The name of the lookup table (grades)

The location of the letter grade in the lookup table (2)

After the equation is entered in N7, click and drag to fill the equation down into the remainder of the gradebook.