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.

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

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

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

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.

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.

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.

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.

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.

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.

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

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.

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 |

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.

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.

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.

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.

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.

Training Services - ACITS

The University of Texas at Austin