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.