This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?
One of the main advantages of creating a computerized gradebook is that the worksheet program can do calculations for you. For example, Excel can calculate the subtotal of points a student earns and then find the percentage of the total points earned.
For a more in-depth discussion on performing calculations in your gradebook, refer to Performing Calculations with Formulas (Windows | Macintosh) or Calculating with Functions.
All calculations must begin with an equals (=) sign.
Cells are referenced by column and row ID. The first cell of the worksheet is cell A1 (column A, row 1).
A colon (:) is used between the cell references to indicate a range (group) of cells. For example, to reference quiz scores in columns D, E, F and G for the student in row 5, the range would be: D5:G5 (D5 through G5).
NOTE: Excel interprets D5:G5 the same as G5:D5.
The Function Wizard/Formula Builder can be used to help walk you through the steps for functions that you are unfamiliar with.
During the semester, you may want to hand out a report to the students showing points earned so far. You can quickly total the points using the SUM function. For more information, refer to Adding a Range of Cells with the SUM Function.
If your final score (or a project or exam grade) is based on a percentage of points earned, you can use Excel to calculate the percentage earned. Just as with a calculator, this is achieved by dividing the earned score by the points possible.
EXAMPLE: If D2 contains the points possible and D3 is the points earned, the formula will be =D3/D2 (points earned/points possible).
To calculate a percentage, divide the total score by the total points possible. If you want to use percentages from 0 to 100, multiply the result by 100.
EXAMPLE:
If the perfect total score is in cell D2, then =D3/$D$2*100 will produce a percentage between 1 and 100 for a student's total score.
When copying this formula, you will want to lock in the denominator (D2). To do this, make the reference absolute by adding a dollar sign ($) before the column and row reference.
EXAMPLE:
Instead of D2, it will be $D$2. When the formula is copied, the numerator (D3) will change relative to the new location and the denominator will always remain D2.
HINT: If your formula does not work, make sure you have the correct cells and/or cell range entered into your formula. To troubleshoot your formulas, refer to Correcting Circular References.