Microsoft Excel 2003/2004
Creating a Weighted Gradebook
This document shows you how to use Excel to create a weighted gradebook. In creating the formula that calculates the final grade, the student’s actual score is divided by the total possible score and then multiplied by the weight (percentage) assigned for that item.
- Formatting a Weighted Gradebook
- Creating the Formula to Calculate a Weighted Gradebook
- Examples of Completed Formulas
Formatting a Weighted Gradebook
Because the formula which calculates the final grade refers to both the total possible points and the weight of an item, you should include this information in your spreadsheet when setting up your gradebook. The following example shows Row 1 displaying the column labels, Row 2 displaying the total possible points for each grade item, and Row 3 displaying the weight for each grade item. 
Creating the Formula to Calculate a Weighted Gradebook
In creating the formula that calculates the final grade, the student’s actual score is divided by the total possible score and then multiplied by the weight (percentage) assigned for that item.
NOTE: If a grade for a grade item (e.g., presentation) has not been entered and you want to calculate the final grade, you must subtract the weight (percentage) for that item from 100% and divide the final score by the difference.
Examples in the following steps refer to the sample gradebook shown here:
-
Open the worksheet containing the gradebook
-
Select the cell where you want the result (final grade) to appear
-
To start creating the formula which will calculate the final grade, press [=]
-
Type two opening parentheses ((
-
Click the cell containing the first grade item to be included in the final score
EXAMPLE: Click cell C4 -
Type a forward slash /
-
Type the absolute cell reference of the cell containing the total score for the grade item
NOTE: For information on absolute vs. relative cell references, refer to Editing Cell Contents.
EXAMPLE: Type $C$2 -
Type one closing parenthesis )
-
Type an asterisk *
-
Type the absolute cell reference of the cell containing the weight for the grade item
For information on absolute vs. relative cell references, refer to Editing Cell Contents.
EXAMPLE: Type $C$3 -
Type one closing parenthesis )
Your formula looks like this: ((C4/$C$2)*$C$3) - To add more grade items,
- Press [+]
-
Repeat steps 4–11
-
For items which do not have a grade entered,
- Subtract the weight of the item from 100%
EXAMPLE: Item weighted at 10%: 100% - 10% = 90% - Place your insertion point at the end of the formula and type a forward slash [/]
-
Type difference obtained in step a
NOTE: Use the decimal format of the percentage
EXAMPLE: ))/.90
- Subtract the weight of the item from 100%
Examples of Completed Formulas
Example: Final Weighted Grade, All Grades Entered
This example shows the completed formula for a student final grade where the grades for all items have been entered.
Example: Final Weighted Grade, One Grade Not Entered
This example shows the completed formula for a student final grade where the grade for the grade item in column M has not been entered.
