# (Legacy) Microsoft Excel 2007: Gradebook: Other Helpful Calculations

Last Updated

This article is based on legacy software.

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.

**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)

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