1 Kaganris

Student Grade Sheet Excel Assignment Formulas

Using Microsoft Excel to Calculate and Manage Grades

4 January
Copyright , Academic Computing and Instructional Technology Services
training@woaknb.wz.sk
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

StudentExam 1Exam 2Exam 3FinalAverage

2

Davis88738578=AVERAGE(B2:E2)

3

Johnson52716567=(B3*)+(C3*)+(D3*)+(E3*)

4

Smith91859689=(SUM(B4:E4)-MIN(B4:E4))/3

5

Jones78826775=(SUM(B5:D5)-MIN(B5:D5))/2* + (E5*)

6

Connor88918375=(SUM(B6:D6)-MIN(B6:D6)-SMALL(B6:D6,2))* + (E6*)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, is an F, 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 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 , , 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.

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.

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

 How to calculate letter grade in Excel?

To assign letter grade for each student based on their scores may be a common task for a teacher, for example, I have a grading scale defined where the score below 60 is an F, 60 to 70 is a D, 70 to 80 is a C, 80 to 90 is a B, and 90 to is an A. In Excel, how could you calculate letter grade based on the numeric score quickly and easily?

Calculate letter grade based on numeric score with IF function

Calculate letter grade based on numeric score with VLOOKUP function


Calculate letter grade based on numeric score with IF function


The following formula can help you to quickly calculate the letter grade as you need, please do as this:

Enter this formula:=IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF(E2>=60,"D","F")))) into a blank cell next to your score data where you want to locate the letter grade, F2, for instance, and then drag the fill handle down to the cells that you want to contain this formula, and the letter grade has been displayed in each cell as follows:

Note: In the above formula, E2is the cell which you want to convert the number to letter grade, and the numbers 90, 80, 70, and 60 are the numbers you need to assign the grading scale. You can modify them as you need.


Calculate letter grade based on numeric score with VLOOKUP function

If there are more grading scales, such as following screenshot, I have defined a grade scale table, and now, I want to apply this grade scale to the score list table. In this case, the VLOOKUP function can help you.

Please enter this formula: =VLOOKUP(B2,$F$1:$G$10,2,TRUE) into a blank cell beside your data, C2, for example, and then drag the fill handle down to the cells that you want to apply this formula, and now, you can see all the letter grades based on the corresponding grade scale table are calculated at once, see screenshot:

Note: In the above formula,B2 is the cell which you want to calculate the letter grade, F1:G10 is the table range you want to look for, the number 2 indicates the lookup table column number which contains the values you want to return.


Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools

         

Leave a Comment

(0 Comments)

Your email address will not be published. Required fields are marked *