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 2007/2008

Gradebook: Other Helpful Calculations

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.

return to topCalculating 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.

Desired calculation Formula to enter
Average =AVERAGE(beginning cell range:end cell range)
Median =MEDIAN(beginning cell range:end cell range)
Maximum =MAX(beginning cell range:end cell range)
Minimum =MIN(beginning cell range:end cell range)

return to topDropping 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 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

  1. Use the SUM function to total all the scores in a selected range

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

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

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

return to topFinding 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 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
Formulas for low scores and average

Based on the above calculations and the values entered in columns D through H, the following graphic shows the calculated results.

Results for low scores and average

Excellence. Our Measure. Our Motto. Our Goal.