Help with Excel Gradebooks
Common Excel Gradebook functions:
1. Add columns for each new assignment/event, and input grades. Excel is composed of “cells” that are identified by row and column (A1 is the top left cell). The student names are already in columns from the roster you downloaded. Column D and beyond are probably empty. Click cell D1 to highlight it and type a name for an assignment you want to add (example: “Quiz 1”). Now manually enter in the scores for each student on that assignment. You may use numbers, percents, letter grades, or anything you want, but most instructors use regular numbers or percentages so that calculating averages is easy. Repeat in a new column for each new grading event.
2. Calculate an average for a specific assignment/event. Excel allows any particular cell to perform a function rather than hold a piece of data. Many instructors choose to put an average at the bottom of a column, beneath all the student scores. Double-click that cell to get a blinking cursor, and type the formula to calculate the average. The general formula is:
=AVERAGE(topcell:bottomcell)
Example: let’s say your D-column is labeled as Quiz 1, and you have student scores from D2 through D17. Double click D18 and type =AVERAGE(D2:D17) and then hit return. The desired numerical average should display in cell D18. Note: you could have put this anywhere. Some instructors leave blank lines before the average, and type in the word “Average” in the cell right next to it, to make obvious what this number is.
3. Calculate a final grade for the semester. There is no single procedure to decide a semester grade, because every class is different in the number of assignments and their respective weights. Many instructors create a column to provide the “weighted” score of each assignment (i.e., an essay that is 15% of the semester grade will have the score of 80 multiplied by .15), and then the various weighted scores can be added up for a semester score.
Consider this example grade breakdown: 40% for chapter tests, 30% for the final exam, 20% for an essay, and 10% for the midterm. You could create a column for each of these and place raw scores there. A column next to each one could calculate the weighted scores. For instance, one student scores a 90 on the essay. In the next column, the formula would refer to the cell number of that raw score of 90, and multiply it by .20 (example: =F20*.2)
We use cell numbers because Excel allows you to quickly replicate that function down the entire column. Just figure out the formula for the person at the top of the list, and hit enter. Using the mouse, highlight the cell you just finished, and move the mouse to the bottom right corner of that highlighted cell; the mouse icon should change from a white plus-sign into a smaller, black plus sign. Click and hold when the small black icon is present, and drag the mouse down the entire column. The weighted score for all students is calculated and auto-filled for you.
Once you have calculated weighted scores for all grades in the semester, the final column needs to add up the relevant columns. Type the “equals” sign and then all of the relevant cell numbers, with plus signs between them (example: =G6+J6+P6+V6+X6). After doing this for the student at the top of the list, again “autofill” (click and drag) down the whole column, and all the class scores will be calculated.
Alternately, raw scores can be computed directly in a final column, but this involves a more complicated algebraic formula.
4. Other common calculations (such as median, maximum, minimum, dropping the lowest score): http://www.uwec.edu/help/Excel03/gr-helpful.htm
Excel Gradebook online tutorials:
Continue Reading . . .
For Further Assistance
If you're having difficulty locating the Grades pagelet or trouble accessing it, contact the UCF Help Desk (407-823-5117 or helpdesk@mail.ucf.edu). The Faculty Center (407-823-3544 or fctl@mail.ucf.edu) is happy to consult with you about further applications or complications using myUCF Grades. Additionally, the Faculty Center offers regular workshops and one-on-one consultations on creating and maintaining Excel gradebooks.
