(Legacy) Microsoft Excel 2007: Gradebook: Other Helpful Calculations
Last Updated
The most essential functions in a gradebook are those that add the point total and find percentages. However, there are other functions that can show information about the whole class such as an average for an exam and the maximum score achieved, or a minimum score of each student so that you can drop the lowest grade.
Calculating Other Functions in Your Gradebook
You may want to calculate the average score for an exam, as well as the median, maximum, and minimum scores. Using the process for entering formulas described in Performing Calculations with Formulas (Win |Mac), type these functions to calculate average, maximum, and minimum scores.
Dropping the Lowest Score
Some instructors like to drop the lowest score(s) for each student to give an allowance for a missed quiz or a bad test-taking day. You can drop a single score or multiple scores with Excel. To calculate the total points while dropping two or more lowest scores, add the scores and then subtract the smallest and the next smallest values. For more information on entering formulas, refer to Calculating Other Functions above or Performing a Database Function (Win | Mac).
WARNING: Blank or empty cells are ignored by the MIN, SMALL, and AVERAGE functions. A zero ( 0 ) is considered a value and can be dropped as the minimum value or smallest score and will be included when an average is calculated.
Dropping the Lowest Score: Single Score Option
In order to drop the lowest score, you will need to use the MIN function. This function locates the minimum value in a range of scores.
EXAMPLE: MIN(D2:H2) will return the smallest value in cells D2 through H2.
Use the SUM function to total all the scores in a selected range.
Subtract the lowest score using the MIN function.
EXAMPLE: =SUM(D2:H2)-MIN(D2:H2)
NOTE: This formula calculates the total of scores in cells D2 through H2, subtracts the lowest value in the range, and displays the result in the cell.
Dropping the Lowest Score: Multiple Score Option
If you want to drop more than one low score, you will probably want to use the SMALL function. The SMALL function will return the n-th smallest value in the given range, where n is the number you specify.
EXAMPLE: SMALL(D2:H2,2) will return the 2nd smallest value in the range.
In the row you want to calculate scores for, use the SUM function to total all the scores in the row.
EXAMPLE: =SUM(D2:H2).
NOTE: This formula calculates the total of all scores in cells D2 through H2 and displays this result in the cell.Subtract the lowest scores using the SMALL function.
EXAMPLE: =SUM(D2:H2)-SMALL(D2:H2,1)-SMALL(D2:H2,2)
NOTE: This formula performs the following calculation: (total of all scores in cells D2 through H2) - (lowest score) - (second lowest score) and displays this result in the cell.
Finding Averages
Excel can figure the average grade for an assignment or exam automatically with the AVERAGE function. To calculate the average of a series of cells, use the AVERAGE function. For more information on entering formulas, refer to Calculating Other Functions above or Performing a Database Function (Win | Mac).
EXAMPLE: =AVERAGE(D2:H2)
NOTE: This formula calculates the average of all scores in cells D2 through H2 and displays this result in the cell.
WARNING: Blank or empty cells are ignored by the MIN, SMALL, and AVERAGE functions. A zero ( 0 ) is considered a value and can be dropped as the minimum value or smallest score and will be included when an average is calculated.
The following graphic shows the calculations for:
Column I: calculating the total with the two lowest scores dropped
Column J: calculating the minimum (lowest) score
Column K: calculating the average of the scores
Based on the above calculations and the values entered in columns D through H, the following graphic shows the calculated results.