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.
- Review of Basics for Excel Calculations
- Calculating a Student's Points Earned
- Calculating Percentage of Total Points Earned
Review of Basics for Excel Calculations
-
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 range (group) of cells use a colon ( : ) between the cell references to indicate through. 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 (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.
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, 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.
