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

Gradebook: Phase 4
Calculating Subtotal Points & Percentages

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.

return to topReview of Basics for Excel Calculations

return to topCalculating a Student's Points Earned

During the semester, you may want to hand out a report (e.g., a progress report) to the students showing points earned so far. You can quickly total the points using the SUM function. For more information on adding cells with the SUM function, refer to Option 1 and Option 2 in Calculating in Functions.

return to topCalculating Percentage of Total Points Earned

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, you will absolute the reference by adding dollar signs ( $ ) 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.

If your formula does not work, make sure you have the correct cells and/or cell range entered into your formula. For more information on troubleshooting your formulas, refer to Correcting Circular References.

Excellence. Our Measure. Our Motto. Our Goal.