Electronic Gradebook

This document illustrates how Microsoft Excel can be used to
create an electronic version of a typical academic gradebook.
Formulas will be demonstrated to calculate total points,
percentages, and a letter grade for all students. In addition, a
sample spreadsheet is available for downloading that can be used
as a 'template' for creating these gradebooks.


    
Introduction
    
Definitions
    
Creation
        of a Gradebook
    
Sample
        Worksheet




Introduction

Spreadsheet programs are useful tools for data analysis,
including simple statistics (averages, maximum/minimum, standard
deviation, etc.) and graphs (pie charts, line graphs, etc.). The
focus of this document is application of these programs as
"electronic gradebooks". With the new SIS enrollment
system, it is now possible to get class rosters on a computer
diskette, which can then be read into a spreadsheet program. It
is possible to add simple functions to the spreadsheet that then
allow automatic calculation of grades, including dropping lowest
scores, adding any desired scaling, and creating graphs such as
grade distributions. 

The two most popular spreadsheet programs in the PC market are
Lotus 1-2-3 and Microsoft Excel, both of which are available on
this campus. While this handout emphasizes use of Excel, Lotus
1-2-3 works in a very similar manner, and most of this document
applies equally well to use of either of these two programs. 



Definitions

Cell: The basic building blocks of the spreadsheet.
Each cell (or box) can hold either a number, text, or a formula. 

Address: Each cell in a spreadsheet is referenced by a
number (signifying the row) and a letter (signifying the column).
For example, cell "B4" is the fourth row (4) of the
second (B) column. 

Absolute address: Always refers to the same cell. If a
formula containing an absolute address reference to another cell
is copied, the reference does not change. In dicated by a dollar
sign ($) proceeding the row, column, or both. (Ex. $B$4). 

Relative address: A relative address reference will
change if a cell containing this type of address is copied. Very
useful for creating formulas that are to be consistently applied
to a large number of rows (or columns). 

Formula: The power of a spreadsheet comes from the fact
that cells can contain formulas that automatically perform
certain calculations, such as the sum of the numbers in a
particular column or the standard deviation of a set of exam
scores. 



Creation of a
Gradebook

The following sequence of steps outlines one possible method
for creating a simple gradebook using Microsoft Excel (Excel).

Obtaining a Roster 

Thanks to the SIS system (and some database programming by Jo
Ellen), it is now very convenient to obtain a spreadsheet
containing student rosters for any class. Bring a diskette to one
of the faculty secretaries in Main Hall and ask for your current
rosters. When you get this diskette back, you should have a
separate file for each class you teach. The filename will be the
course number with a ".XLS" extension added. Use
"Windows Explorer" to copy this program into the
appropriate subdirectory on your hard drive.

Reading data 

Start Excel, and use the File Open
command to read the file. (Alternatively, you can use
"Windows Explorer" and double-click on the file.) You
should now see your roster on the main window, which will include
several columns of information that you probably do not want. To
remove a column, click on the column heading (the letter) with
your mouse. The entire column should turn dark. Then, from the
menu, select Edit Delete.
Repeat for all unwanted columns. 

I like to have a row of cells containing the course name and
semester at the top of my gradebooks. To create these, select the
first row by clicking on the "1" on the left
side. Add one or more rows by use of the Insert Row
command. To add a title, select a cell near the middle of the
first row (ex. E1) with your mouse and type in the course name.
When you are satisfied, save the file with a new filename using
the File Save As
command.

Creating Headings 

For the present example, we will assume the course grade is
based on the total points obtained from the best two out of three
25-point quizzes (i.e., the lowest quiz score is dropped), three
100-point exams, and one 150-point final for a grand total of 500
possible points. The grading scale will be based on 90-100% = A,
80-89% = B, etc. 

In the first available column, enter column headings for the
three quizzes, the quiz total, three exams, a final exam, total
points, percentage, and grade. See the sample on
the last page of this worksheet for an example of how this might
look. 

Entering Formulas 

All formulas given will be based on the rows and columns
indicated in the screen shot
shown below. The quotes around each formula should not
be entered. Formulas may need to be adjusted slightly to account
for differences in row and/or column references. Formulas will
only be entered for the first student. Once these are correct,
they can be "copied" to the remaining rows. 

Total: The total number of points obtained is equal to
the sum of the quiz total, the three exams, and the final. In
cell K5, enter the formula "=sum(F5:J5)". The
formula "=sum()" indicates that the sum total of the
values present in the range given will be calculated. In this
example, the range F5:J5 refers to the two cells listed (F5 &
J5) and all cells in between (G5, H5, and I5).

Quiz Total: Cell F5 should contain the quiz total
points obtained on quizzes. If no quizzes will be dropped, the
formula for this cell is simply "=sum(C5:E5)".
However, if we want to drop the lowest quiz, then this column
will contain the best two out of three scores obtained on the
three quizzes. To find this, we will take the total of all three
quizzes and drop the lowest score. In cell F5, we could use the
formula "=sum(C5:E5)­min(C5:E5)". This formula
takes the total points obtained on all quizzes taken and
subtracts the score obtained on the lowest quiz. Unfortunately,
this approach will fail if a student doesn't take one or more of
the three quizzes unless a grade of zero is manually entered.
A blank cell (or a cell containing text) will be ignored and will
not count as the minimum. To correct this problem, a slightly
more complicated formula should be used. In cell F5, add the
formula "=if(counta(C5:E5)<3,sum(C5:E5),sum(C5:E5)­min(C5:E5))".
The format of the "if" statement is: =if(test, operation if
test is true, operation if test is
false). (Color coding shown only to help show the logic of
the 'if' statement).

Percentage: The simplest approach is to recognize that
there are 500 points possible. The percentage in cell L5 will
then be "=K5/500". An alternative approach is to
store the total points in a cell near the bottom of a column that
contains the total points actually possible on any given date
(keep a running total throughout the semester). In this case, the
formula in cell L5 would be "=K5/$L$15". Note
that the running total in cell L15 must be given an absolute
reference ($L$15).

Grade: To calculate a letter grade, it is first
necessary to create cells containing the numerical thresholds for
each grade (as shown below). This allows any necessary
adjustments to be easily applied in a consistent manner at the
end of the semester. References to these values must be absolute
references. After adding these threshold values in cells D23 to
H23, place the following formula (without the line break) in cell
M5: "=HLOOKUP(L5,$D$21:$H$23,3)".

This statement says to compare the value in cell L5 (which should
contain the student percentage) with the values in cells $D$21,
$E$21, $F$21, $G$21, and $H$21 (which must be sorted in ascending
order). If the value in cell L5 is less than or equal to the
value in cell $D$21, then insert the contents of the third row of
the range given (in this case, cell $D$23). Else, if the value of
cell L5 is between the values of cells $D$21 and $E$21, then use
the value in cell $E$23. Repeat to the end of the table. While
the logic may not be obvious, the formula is very easy to use
once the grade matrix (cells $D$21 through $H$23) are filled in.
If you decide later that the grading scale needs to be changed
(for example, you will let a 69.0% count as a "C"),
this can be accomplished by changing the contents of only one
cell (in this example, cell $F$21).

Copying formulas: After all formulas have been entered
for the first student, select cells F5 to M5 with the mouse.
(Click on cell F5, drag to cell M5, and then release the mouse
button.) Select Edit Copy
from the menu (or <ctrl> C). Then, highlight cells F6 to
M13 with the mouse. Finally, select Edit Paste
(or <ctrl> V). 

Keeping a Running Total: After the last student, skip a
row and add a new student named "Points". When the
scores for each quiz or exam are entered, add the total possible
points to the appropriate column in the "Points" row.
Under the Total column (K15), place the formula "=sum(F15:J15)".
Be sure to adjust the quiz total in cell F15 to only include the
top two quizzes. (Perhaps the best method is to copy the formula
from cell F5 to cell F15).

Sample
Gradebook 



Formulas used:

F5: =if(COUNTA(C5:E5)<3,SUM(C5:E5),SUM(C5:E5)-MIN(C5:E5))

K5: =SUM(F5:J5)

L5: =K5/$K$15

M5: =HLOOKUP(L5,$D$21:$H$23,3)

Formulas in rows 6-13 copied
from row 5

F15: copied from cell F5

K15: =SUM(F15:J15)

C16: =AVERAGE(C5:C13)

C17: =COUNTA(C5:C13)

C18: =MAX(C5:C13)

C19: =MIN(C5:C13)

D24: =DCOUNTA($A$4:$M$13,$M$4,D$21:D$22)

Sample Worksheet

If you would like to download a file containing all of these
formulas, select the link below. When prompted, save the file in
a convenient directory.

Download sample.exe.
(~21 Kb)

After downloading this file, it must be renamed to sample.xls
in order to be used by Microsoft Excel. (Most browsers assume
files with an ".EXE" extension contain data in a binary
format and are downloaded unchanged, which is the desired result
for this file. Files containing the ".XLS" extension do
not appear to be saved correctly by many browsers).










This page was last modified on August
27, 1997

and was written and is maintained by Clarke Earley

Kent State University
Stark Campus

Department
of Chemistry

email: cearley@stark.kent.edu


© 1997 by Clarke Earley, Kent State
University.