Using Microsoft Excel to Calculate and Manage Grades

Macintosh and Windows

4 January 1999
Copyright 1997, Academic Computing and Instructional Technology Services
training@cc.utexas.edu
The University of Texas at Austin

This handout discusses features of Microsoft Excel that are useful in computing and managing grades. These features include calculating averages and standard deviations, dropping low test scores, assigning letter grades, and creating frequency distributions. The handout assumes that you are able to enter and edit data in Excel and create basic formulas and functions.

Statistical Functions and Weighted Averages

Excel has numerous functions to summarize data. The AVERAGE( ) function calculates the average of a group of numbers. For example in cell F2 below, =AVERAGE(B2:E2) calculates the average for the student in row 2. This AVERAGE( ) function assumes that all test scores are weighted equally. It also ignores blank cells; it does not treat them as 0. Create AVERAGE( ) functions by either typing them manually or using Excelís Paste Function tool.

 

A

B

C

D

E

F

1

Student Exam 1 Exam 2 Exam 3 Final Average

2

Davis 88 73 85 78 =AVERAGE(B2:E2)

3

Johnson 52 71 65 67 =(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40)

4

Smith 91 85 96 89 =(SUM(B4:E4)-MIN(B4:E4))/3

5

Jones 78 82 67 75 =(SUM(B5:D5)-MIN(B5:D5))/2*.60 + (E5*.40)

6

Connor 88 91 83 75 =(SUM(B6:D6)-MIN(B6:D6)-SMALL(B6:D6,2))*.50 + (E6*.50)

 

Weighted Averages

There are many methods to create a weighted average using formulas. For example, if you want to count each test 20% and the final 40%, use the following formula:

=(B3*.20)+(C3*.20)+(D3*.20)+(E3*.40).

You can easily modify this formula to accommodate a different grading scheme. If each test counts 15% and the final counts 55% the formula would be:

=(B3*.15)+(C3*.15)+(D3*.15)+(E3*.55).

Dropping Low Scores

Dropping the lowest test or quiz score is a common grading practice. Excelís MIN( ) function is useful in this type of calculation. The MIN( ) function returns the smallest value in a range of cells. The formula in cell F4 above adds all test scores for Smith then subtracts the minimum test score and divides the result by 3. This formula also assumes that all scores are weighted equally. However, you can calculate a weighted average and drop a low score. Letís say tests count 60% and the final counts 40%. You also let students drop their lowest test score. In cell F5 above the formula for this example is:

=(SUM(B5:D5)-MIN(B5:D5))/2*.60 + (E5*.40).

Excelís SMALL( ) function is also helpful in determining the second or third lowest score. The syntax of the SMALL( ) function is

=SMALL(data,n)

where data is the range of cells containing the data and n represents the nth smallest number in the data set. For example to find the second lowest test score for Connor in the previous example use SMALL(B6:E6,2); to determine the third lowest score use SMALL(B6:E6,3). If you need to calculate an average that drops the two lowest scores use MIN( ) to subtract the lowest and use SMALL( ) to subtract the second lowest. The formula in cell F6 in the table above drops the two lowest exam scores and calculates the average with the final and the remaining Exam score counting 50% each.

The table below lists several of the more common and helpful statistical functions in Microsoft Excel.

FUNCTION

WHAT IT DOES

SUM(range ) Adds a range of cells
AVERAGE( range) Calculates the average of a range of cells
MAX(range ) Returns the maximum value of a range
MIN(range ) Returns the minimum value of a range
COUNT(range ) Counts the number of values (cells containing numbers) in a range
COUNTA(range ) Counts the number of non-blank cells within a range
COUNTBLANK(range ) Counts the number of blank cells within a range
COUNTIF(range, "string" ) Counts the number of cells that are the same as a search string
STDEV(range) Calculate the standard deviation of a sample
SMALL(range, n) Returns the nth smallest number in the specified range

You will probably use the SUM( ) and AVERAGE( ) functions most frequently. As stated above, the COUNT( ) function counts the number of cells containing numbers. The COUNT( ) function ignores blank cells and does not count cells that contain text labels. To count cells containing text, you must use the COUNTA( ) function.

Conditional Calculations and Lookups

Excel has several logical functions that let you test cells and perform different operations depending on their contents.

IF( ) function

The IF() function enables you to specify two different calculations based on a certain condition. The syntax of the IF( ) function is

=IF(condition, calculation if condition is true, calculation if condition is false)

If the condition specified in the first argument is true, Excel performs the calculation specified in the second argument, otherwise Excel calculates the third argument. For example, in the figure below suppose you have a liberal grading policy that allows students to replace an exam score if they improve. Specifically, if a student scores higher on Exam 3, their adjusted Exam 2 score is the average of Exams 2 and 3. If they did not score higher on Exam 3, then their adjusted Exam 2 score is their actual Exam 2 score. The IF( ) function in cell E2 below performs this calculation.

 

A

B

C

D

E

F

1

Student Exam 1 Adjusted
Exam 1
Exam 2 Adjusted
Exam 2
Exam 3

2

Davis 88   73 =IF(F2>D2,(F2+D2)/2,D2) 78

3

Johnson 52   71   67

4

Smith 91   85   89

5

Jones 78       75

6

Connor 88   91   75

The first argument, F2>D2, tests whether the student scored better on Exam 3. If the condition is true, the second calculation is performed (the average of the two exams). If the condition is false, the adjusted score is simply the actual score on the exam, D2 in this case.

AND( ) and OR( ) functions

To specify multiple conditions within an IF( ) function, use Excel's AND( ) and OR( ) functions.

The syntax of these functions is:

=AND(condition 1, condition 2, ...condition n)

= OR(condition 1, condition 2, ...condition n)

AND( ) returns the value of TRUE if all its conditions are true, and returns FALSE otherwise. OR( ) returns TRUE if at least one of the specified conditions is true.

ISBLANK( ) function

Excelís ISBLANK( ) function tests whether a certain cell is blank,. This function returns TRUE if the cell is blank and FALSE if itís not.

Use the AND( ) function and the ISBLANK( ) functions to modify the previous grading scheme. Assume a student only gets to improve their Exam 2 score if they actually took Exam 2 (i.e. Exam 2 is not blank) and they did better on Exam 3. This formula would be =IF(AND(F3>D3,NOT(ISBLANK(D3)),AVERAGE(F3,D3),D3).

The first argument of the IF( ) function is a AND( ) function that tests both conditions, Exam 3 is greater than Exam 2 and Exam 2 is not blank. If both conditions are true, Excel performs the second calculation, otherwise Excel performs the calculation in the third argument.

Lookup Tables

The IF( ) function is very useful, but it is limited to either TRUE or FALSE outcomes. In many worksheets, you might want to create a function that handles multiple outcomes. Excel's VLOOKUP( ) function is ideally suited for this sort of calculation.

With the VLOOKUP( ) function (short for vertical lookup) you can specify lookup values for different outcomes. For example, if you have a list of numeric averages in a worksheet, you can create a formula that assigns letter grades based on a student's numeric score (e.g. a score of 76 would be a C).

To use a VLOOKUP( ) function, you must first create a lookup table with a range of values. This lookup table is similar in concept to a tax table. When using a tax table, find your income in the first column and then read across to the column that applies to you, single, married etc. A sample lookup table to handle letter grades appears in cells E2:G6 below. In this case, 0-59 is an F, 59-69 is a D, and score above 89 is an A.

  A B C D E F G
1   Average Grade   Lookup Grade Comment
2 Jones 76 =VLOOKUP(B2,$E$2:$G$6,2)   0 F Failing
3 Williams 85     59 D Poor
4 Larkin 92     69 C Fair
5 Piniella 58     79 B Good
6 Jordan 63     89 A Excellent
7 Smith 77          
8              

 

After you create a lookup table in a blank area of your worksheet, construct a lookup function. The basic syntax of the VLOOKUP( ) function is:

=VLOOKUP(lookup value, lookup table range, value column)
Arg 1 Arg 2 Arg 3

The first argument, the lookup value, is the value you wish to look for in the lookup table. In the example above, it is the student's numeric score in column B. The lookup table range is the range on the worksheet that contains the lookup table, cells E2:G6 in the example. You should not include descriptive column or row headings in the range used for argument 2. Finally, the value column in argument 3 tells Excel which column of the table to use for the actual result. Specify the value column by indicating what numeric column of the lookup table to use. In the Grades lookup table above, use column 2 if you want letter grades and column 3 if you prefer comments like Excellent or Poor.

The lookup function to calculate a final letter grade for Jones in cell C2 is =VLOOKUP(B2,$E$2:$G$6,2). Jones' grade is 76. Excel looks for this value in the first column, lookup table range. Since it does not find 76 it looks for the largest value that is less than 76, which is 69 in the table. Excel then reads the value from the appropriate value column corresponding to the 69 score.

Note: The first column of a vertical lookup table must be in sorted order.

Analysis ToolPak

Microsoft Excel has numerous Add-in features that support statistical analysis. Two specific tools are useful in generating descriptive statistics and histograms of grade distributions. To access these features the Analysis ToolPak must be loaded.

Loading the Analysis ToolPak

Open the Tools menu in Excel. If the Data Analysis menu appears near the bottom, the Analysis ToolPak is already loaded. If the menu is not visible, choose Add-Ins from the Tools menu. In the Add-Ins dialog box click the checkbox next to Analysis ToolPak and click OK.

Add Ins Dialog Box

After some screen flickering and disk churning, the Data Analysis menu should appear on the Tools menu.

Descriptive Statistics

The Descriptive Statistics tool generates simple descriptive statistics like average, median, and standard deviation for a collection of data. To generate these statistics, choose Tools and Data Analysis. In the Data Analysis dialog box select Descriptive Statistics. In the Descriptive Statistics dialog box (see below), specify the cells that contain your data in the Input Range box. Click the Summary Statistics checkbox in the lower left corner. By default, Excel generates the statistics on a new worksheet. If you want the statistics to appear on the same worksheet, click the Output Range button and specify a destination cell for the statistics.

Descriptive Statistics

Sample output from the Descriptive Statistics tool appears in the table below.

Mean

13767.827

Standard Error

313.7244427

Median

11550

Mode

12300

Standard Deviation

6830.264586

Sample Variance

46652514.31

Kurtosis

5.377822396

Skewness

2.124606282

Range

47700

Minimum

6300

Maximum

54000

Sum

6525950

Count

474

Confidence Level(95.000%)

614.8876984

 

Histogram

The descriptive statistics above reported skewness and kurtosis, which both concern the shape of the data distribution. Excel also letís you plot this visually by using the Histogram function in the Analysis ToolPak.

Generating the Bin Range for a Histogram

The Histogram tool requires that a Bin Range or list of categories be specified. The Bin Range represents the categories for which you want frequency accounts. For example in a grading situation, the Bin Range might include all possible test scores. Or you might only list ranges of scores like the table below.

The Bin Range 1 would count how many people scored 1, 2, 3, etc. Bin Range 2 would count how many people scored 0-2, 2-4, 4-6 etc.

Bin Range 1

 

Bin Range 2

1

 

0

2

 

2

3

 

4

4

 

6

5

 

8

6

 

10

7

   

8

   

9

   

10

   

Use Excelís Fill tool to help create this Bin Range for a histogram. Start by entering the lowest possible test score in a cell. You can enter 0 or use the MIN( ) function to calculate the actual minimum. With that minimum cell selected, choose Edit then Fill Series.

Fill Series Dialog Box

In the Series dialog box, select columns and Linear for the Type. Enter the appropriate Step and Stop values and click OK. The Step Value specifies how much to increase each entry and the Stop value indicates when to stop the series. To generate the series from 1 to 10 above, you would choose 1 for the Step Value and 10 for the Stop Value. For Bin Range 2 above, select 2 for the Step value and 10 for the Stop Value.

Creating the Histogram

After you create the Bin Range, generate the actual histogram. Choose Tools and Data Analysis, and select Histogram in the Data Analysis dialog box.

In the Histogram dialog box, the Input Range is the actual data you want to summarize, for example the list of all test scores. The Bin Range is the range you created with the different categories.

Histogram Dialog Box

Click the Chart Output box in the bottom of the dialog box and click OK. Excel produces a frequency distribution and a chart on another worksheet. A sample histogram appears below.

Histogram Plot

 

 


Training Services - ACITS
The University of Texas at Austin