Microsoft Excel 2007
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
- Calculating a Student's Points Earned
- Calculating Percentage of Total Points Earned
Reviewing Calculation Basics
For a more in-depth discussion on performing calculations in your gradebook, refer to Performing Calculations with Formulas 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 Paste Function Wizard 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.
