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?

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

## Reviewing Calculation Basics

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.

## Calculating a Student's Points Earned

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.

## Calculating 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, 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.