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?

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.

return to topFormatting 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. Example Gradebook setup

return to topCreating 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:
Example Gradebook setup

  1. Open the worksheet containing the gradebook

  2. Select the cell where you want the result (final grade) to appear

  3. To start creating the formula which will calculate the final grade, press [=]

  4. Type two opening parentheses ((

  5. Click the cell containing the first grade item to be included in the final score
    EXAMPLE: Click cell C4

  6. Type a forward slash /

  7. 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

  8. Type one closing parenthesis )

  9. Type an asterisk *

  10. 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

  11. Type one closing parenthesis )
    Your formula looks like this: ((C4/$C$2)*$C$3)

  12. To add more grade items,
    1. Press [+]
    2. Repeat steps 4–11

  13. For items which do not have a grade entered,
    1. Subtract the weight of the item from 100%
      EXAMPLE: Item weighted at 10%: 100% - 10% = 90%
    2. Place your insertion point at the end of the formula and type a forward slash [/]
    3. Type difference obtained in step a
      NOTE: Use the decimal format of the percentage
      EXAMPLE: ))/.90
      Example of completed formula

return to topExamples 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 gradebook all items 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.
Example gradebook item not graded

Excellence. Our Measure. Our Motto. Our Goal.