Using Microsoft Excel to Calculate and Manage Grades

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:

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:

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:

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

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.

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.

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

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.

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.

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

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.