Manage your Grades Electronically with Excel Skip Main Menu

Calculate Grades

This page will give examples for using different Excel formulas or functions to calculate grades in various grading schemes.

This Page

  1. How Excel Formulas Work
  2. Summing Up Scores
  3. Calculating a Numeric Percentage
  4. Converting Numeric Percentage to Letter Grades
  5. Weighting Assignments by Points
  6. Weighting Assignments by Percentage
  7. Dropping Lowest Score
  8. Dropping the Lowest Two (or More) Scores
  9. Keeping the Highest Score
  10. Keeping the Highest Two (or More) Scores
  11. Maintaining a Running Grade
  12. Additional Formulas (Appendix Page)

How Excel Formulas Work

Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.

Formulas used in Excel must follow a certain syntax.

  1. All formulas begin with an equals sign (=).
  2. Some formulas use operands such as +,–, *,/ for addition, subtraction, multiplication or division.
    For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3.
  3. Other formulas refer to different functions such as SUM, AVERAGE and others.
    For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3.
  4. Formulas can be combined with operands.
    For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10.
  5. Functions can be nested within each other.
    For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.

Top of Page

Summing Up Scores

For this discussion, let's assume there are six assignments that need to be added. To add a range of scores use the SUM function and define a range. The student names are in the first column (A) and the assignment titles are in the first row (1). Columns 2-7 are the assignment scores.

Cell B1 would contain the student's name and cells B2 through B7 would contain scores for that student's individual assignments. See mockup spreadsheet below.

Mock Student Spreadsheet: Individual Assigment Scores Entered
  A B C D E F G H
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals
2 Picard, J. 100 90 95 100 85 90  
3 Riker, W. 90 85 80 90 85 100  
4 Troi, D. 100 100 95 100 100 95  

Basic SUM Function

  1. Designate Column H as the "Totals" column. You can fill in a title in Cell A8.
  2. Move your cursor to Cell H2, the second row in the "Totals" column.
  3. Type the function =SUM(B2:G2) into Cell B8. The scores for the first student will be totaled and shown as a number.

Fill Down Formula for Other Students

You can "populate" other cells with the same formula with the Fill Down command.

  1. Highlight all cells in column H with student scores. For instance, if your class has 15 students, you would need to highlight Cells H2 through H16.
  2. Press Control+D (Win/Mac). This will copy the formula in all the cells but change the row numbers. See the box below for alternate instructions from the toolbar or menu.

    Office 2007 (Win): On the Home tab in the Editing group, select the Fill icon (down arrow) then Down.
    Macintosh/Pre-Office 2007 (Win): From the Edit menu, select Fill then Down.

Here is the mock spreadsheet with relevant formulas for all students.

Mock Student Spreadsheet: Formulas for Totals Entered
  A B C D E F G H
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals
2 Picard, J. 20 18 19 20 17 18 =SUM(B2:G2)
3 Riker, W. 18 17 16 18 17 20 =SUM(B3:G3)
4 Troi, D. 20 20 19 20 20 19 =SUM(B4:G4)

 

Top of Page

Calculating a Percent

For this class, let us assume that the course grade is based on six assignments each worth 20 points. The percentage would be the sum of all assignments divided by the maximum total of points. Here is one way to insert the formula.

Percentage of Sum

  1. Insert another row after the Totals column and label it "Percentage". This will be column I.
  2. Move your cursor to cell I2 (first student row) and insert the formula =H2/120 (6 assignments times 20 points each is 120 points total). Since cell H2 contains the total, the formula will access the total points earned by the student. A ratio (e.g. .9333) will be displayed.
  3. Highlight all cells in column I with student scores then press Control+D (Win/Mac) to fill in the percentage formula in all the cells.
    Note: The fill down command is also available in the Editing toolbar (Office 2007/Win) or the Edit menu (Mac/older Win).
  4. To change the decimals to percentages, highlight the cells with the percentage formula and press Control+1 (Win) or Command+1 (Mac). Click the Numbers tab and choose Percentage in the left column. You can also select how many decimal places should be displayed.
    Note: The format as percentage option is also available in the toolbars.
Mock Student Spreadsheet with Percentage Formula for Assigments
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals Percent
2 Picard, J. 20 18 19 20 17 18 112 =H2/120
3 Riker, W. 18 17 16 18 17 20 106 =H3/120
4 Troi, D. 20 20 19 20 20 19 117 =H4/120

 

Alternate Formulas

In addition to the formula specified above, you could use one of these formulas in the first row.

See the running grade section for another method of calculating percentages.

Top of Page

Converting Percentages to Letter Grades

This section explains how to use the IF formula to specify a letter grade based on a numeric range. This section will explain a simple "Pass/Fail" formula before moving on to letter grades, or you can skip to the final formula.

Syntax of the IF Formula

Unlike the previous formulas, the IF formula does not calculate a number, but performs an action based on whether whether a condition in another cell meets a specified criteria or not. If the criteria is met, the formula causes one action to happen. Otherwise, some other action happens.

The IF formula requires three parts - the criteria, action if criteria is met and alternate action.
The syntax is =IF(Criteria, Action if Criteria Met, Other Action)

Pass/Fail Formula

In the Pass/Fail scenario, instructors designate a minimum passing grade. If the percentage is above the minimum, the student passes. Otherwise the student fails.

Returning to the spreadsheet example, recall that the percentages are listed in column I. Let's assume that the minimum passing grade is 70% or "PASS", otherwise it is "FAIL." The formula for the first student would be:

This formula will refer to cell I2. If the number is 70 or higher, then the cell will contain the text "PASS". Otherwise, the cell will contain "FAIL".

Mock Student Spreadsheet with PASS/FAIL Formula
  A H I J
1 Name Totals Percent Pass/Fail
2 Picard, J. 112 93.3% =IF(I2>=70%,"PASS","FAIL")
3 Riker, W. 106 88.3% =IF(I3>=70%,"PASS","FAIL")
4 Troi, D. 117 97.5% =IF(I4>=70%,"PASS","FAIL")
5 Zellig,W. 83 69.2% =IF(I5>=70%,"PASS","FAIL")

Here is the mockup spreadsheet showing just the totals and percentages.

Possible Glitches

Simple Letter Grades

When calculating letter grades, the formula used evaluates a series of nested IF formulas. First it checks to see if the score is 90% or above. If yes, the cell gets an "A", otherwise, it checks to see if the grade is above 80%. If the score is not a "B", it sees if the score is 70% or above and so on.

Formula

Adding the Plus and Minus

In theory, you could expand the nested formula above to include more steps for A-, B+, B- and so forth. Unfortunately, Excel only allows "nested" levels, so the formula must be split into two parts.

For this exercise, we assume the following grading scale

  1. Assuming the worksheet previously used, insert the following formula into cell J2.
    =IF(I2>=95%,"A",IF(I2>=90%,"A-",IF(I2>=88%,"B+",IF(I2>=83%,"B",IF(I2>=80%,"B-","")))))
    This formula calculates grades down to B-. If the score is not a B-, then the cell is left blank ("").
  2. In cell K2, fill in this formula:
    =IF(J2<>"",J2,IF(I2>=75%,"C+",IF(I2>=70%,"C",IF(I2>=60%,"D","F"))))
    This formulas checks to see if there is a score in Column J (J2<>""). If there is a score, it copies the value in cell J2; otherwise it calculates grades for C-F.

Here is a copy of the mock spreadsheet showing the results of both formulas.

Mock Student Spreadsheet with Letter Grade Results
  A H I J K
1 Name Totals Percent Intermediate Final Letter Grade
2 Picard, J. 112 93.3% A- A-
3 Riker, W. 106 88.3% B+ B+
4 Troi, D. 117 97.5% A A
5 Zellig,W. 83 69.2%   D

 

Top of Page

Weighting Assignments: Total Points

Most courses weight course work so that each part is worth a different percentage. In Excel, this can be treated in two different ways - by a total points scale or by assigning weights to parts of a score. Both systems have their advantages and disadvantages.

Assume that you have a class with the following grading scheme.

Requirements by Percentage
Assignment Percentage
10 Problem Sets 45%
Midterm 20%
Final Exam 30%
Participation/Attendance 5%

One way to keep track of grades throughout the semester is to assign a total number of points to the course (often 1000) and divide them among the assignments in a equal percentage.

Requirements by Percentage
Assignment Points
10 Problem Sets 450 (10 assignments, 45 points each)
Midterm 200 points
Final Exam 300 points
Participation/Attendance 50 points (1-2 points per class)

For a final percentage, the scores will be calculated by simply adding points with the SUM function and dividing by 1000. See mock spreadsheet below.

Mock Student Spreadsheet with Total Points (Col B-K are problem set scores)
  A L M N O P Q
1 Name All Problem Sets Midterm Final Participation Percentage Formula Num
2 Picard, J. 430 180 276 50 =SUM(L2:02)/1000 93.6%
3 Riker, W. 415 200 280 30 =SUM(L3:03)/1000 92.5%
4 Troi, D. 438 187 296 50 =SUM(L4:04)/1000 97.0%
5 Zellig,W. 365 155 220 30 =SUM(L4:04)/1000 77.7%

Letter Grades

Use the letter grade formula above to convert percentages to grades.

Advantages to Total Points

Disadvantages

Top of Page

Weighting By Percentage

Instead of dividing course grades into a number of points, you can add the composite of weighted percentages. This requires you to track of the percentage for each individual assignment or group assignments then to add them.

Assume that you have a class with the following grading scheme.

Requirements by Percentage
Assignment Percentage
10 Problem Sets 45%
Midterm 20%
Final Exam 30%
Participation/Attendance 5%

To Add Weighted Assignments

  1. Determine maximum points for each assignments or group of assignments.
  2. Divide each score or group of scores by the maximum number of points to determine a total percentage for that section. For instance, if the final was worth 150 points, then the final exam percentage would be the score divided by 150 (=Q2/150 in the mock spreadsheet below).
  3. For the final percentage, multiply each percentage by its weight (in decimal format), then add the scores. For instance, the formula for the final percentage in the mock spreadsheet would be calculated as:
    =(.45*M2)+(.2*O2)+(.3*Q2)+(.05*S2)
    Note: Percentages like 5% must be divided by 100 to be converted to decimal (.05).

See the mock spreadsheet below.

Mock Student Spreadsheet with Total Points (Col B-K are problem set scores)
  A L M N O P Q R S T
1 Name All Problem Sets %age Midterm %age Final %age Part. %age Final
2 Picard, J. 430 95.56% 90 90.00% 138 92.00% 5 100% 93.6%
3 Riker, W. 415 92.22% 100 100.00% 140 93.33% 3 60% 92.5%
4 Troi, D. 438 97.33% 93.5 93.50% 148 98.67% 5 100% 97.0%
5 Zellig,W. 365 81.11% 77.5 77.5% 110 73.33% 3 60% 77.7%
6 Max Points 450   100   150   5    

Letter Grades

Use the letter grade formula above to convert percentages to grades.

Advantages to Weighted Percentages

Disadvantages

Top of Page

Drop the Lowest Score

Many courses with weekly assignments allow students to drop the lowest score.

  1. Group weekly assignments in adjacent columns (e.g. six assignments in Columns B through G).
  2. In the next column to the right, insert the following formula will add total points and subtract the lowest points
    =SUM(B2:G2)-MIN(B2:G2)
  3. When calculating a final percentage, make sure the total points subtracts points for the dropped assignment. For instance, if you give six assignments worth 20 points, the maximum points is 100 (5 x 20), not 120.

See Mock Spreadsheet

Mock Student Spreadsheet with Drop Formula
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals Percent
2 Picard, J. 20 18 19 20 17 18 =SUM(B2:G2)-MIN(B2:G2) =H2/100
3 Riker, W. 18 17 16 18 17 20 =SUM(B3:G3)-MIN(B3:G3) =H3/100
4 Troi, D. 20 20 19 20 20 19 =SUM(B4:G4)-MIN(B4:G4) =H4/100

Top of Page

Drop the Lowest Two (or More) Scores

In this case, you use the SMALL function to find the first smallest and second smallest scores, then subtract them from the total. The first part of the SMALL fourmula specifies the range, and the second part specifies the rank with "1" being the lowest and "2" being the second lowest.

  1. Group weekly assignments in adjacent columns (e.g. six assignments in Columns B through G).
  2. In the next column to the right, insert the following formula will add total points and subtract the sum of the lowest and second lowest scores.
    =SUM(B2:G2)-((SMALL(B2:G2,1)+SMALL (B2:G2,2))
  3. To drop additional scores, subtract additional SMALL functions. For instance, SMALL(B2:G2,3) would find the third smallest score and the drop lowest three formula would be
    =SUM(B2:G2)-((SMALL(B2:G2,1)+SMALL (B2:G2,2)+SMALL(B2:G2,3)).
  4. When calculating a final percentage, make sure the total points subtracts points for the dropped assignment. For instance, if you give six assignments worth 20 points and dropped two of them, the maximum points is 80 (4 x 20), not 120.

See Mock Spreadsheet

Mock Student Spreadsheet with Drop Formula
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals Percent
2 Picard, J. 20 18 19 20 17 18 =SUM(B2:G2)-((SMALL(B2:G2,1)+SMALL (B2:G2,2)) =H2/80
3 Riker, W. 18 17 16 18 17 20 =SUM(B3:G3)-((SMALL(B3:G3,1)+SMALL (B3:G3,2)) =H3/80

Top of Page

Keep the Highest Score

Some courses may allow students to keep the highest score in a set of practice quizzes.

  1. Group the assignments in adjacent columns (e.g. six assignments in Columns B through G).
  2. In the next column to the right, insert following formula =MAX(B2:G2).
  3. Make sure the percentage in column I is calculated based on the maximum number of points for one assignment.

See Mock Spreadsheet

Mock Student Spreadsheet with Max Formula
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Highest Score Percent
2 Picard, J. 20 18 19 20 17 18 =MAX(B2:G2)-MIN(B2:G2) =H2/20
3 Riker, W. 18 17 16 18 17 20 =SUM(B3:G3)-MIN(B3:G2) =H3/20
4 Troi, D. 20 20 19 20 20 19 =SUM(B4:G4)-MIN(B4:G4) =H4/20

Top of Page

Keep the Highest Two (or More) Score

Some courses may allow students to keep the highest score in a set of practice quizzes.

  1. Group the assignments in adjacent columns (e.g. six assignments in Columns B through G).
  2. In the next column to the right, insert following formula =LARGE(B2:G2,1)+LARGE(B2:G2,2) This formula adds the first highest and second highest score.
  3. To add additional high, add additional LARGE functions. For instance, LARGE(B2:G2,3) would find the third highest score and the keep the highest three scores formula would be
    =LARGE(B2:G2,1)+LARGE(B1:G2,2)+LARGE(B1:G2,3).
  4. Make sure the percentage in column I is calculated based on the maximum number of points for number of assignments scored. Here the maximum would be 40 points (2 x 20).

See Mock Spreadsheet

Mock Student Spreadsheet with Max Formula
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Highest Score Percent
2 Picard, J. 20 18 19 20 17 18 =LARGE(B2:G2,1)+LARGE(B2:G2,2) =H2/40
3 Riker, W. 18 17 16 18 17 20 =LARGE(B3:G3,1)+LARGE(B3:G3,2) =H3/40
4 Troi, D. 20 20 19 20 20 19 =LARGE(B4:G4,1)+LARGE(B4:G4,2) =H4/40

Top of Page

Maintaining a Running Score

One way to track how well students are doing throughout the semester is to create a row which tracks maximum points for each assignment. Percentages can be calculated by referring to the sum of the scores divided by the maximum number of points.

  1. In your spreadsheet, insert a row at the top or bottom and name it "Max Points". In the mock spreadsheet, I will insert the Max Points row at the top (Row 2).
  2. For each assignment, enter the maximum point value.
  3. Use the same formulas for adding/dropping scores that you would for student scores. This will generate the maximum number of points that can be earned throughout the semester.
  4. When calculating percentages, divide by the values in the Max Points Row.
  5. To ensure that the reference in the formula refers to the same row, use the "$" symbol before the row number (e.g. =H3/H$2). When the formula is copied through Fill Down, the row reference remains the same.
Mock Max Points Spreadsheet with Drop Formula
  A B C D E F G H I
1 Name Ass #1 Ass #2 Ass #3 Ass #4 Ass# 5 Ass#6 Totals Percent
2 Max Points 20 20 20 20 20 20 =SUM(B2:G2)-MIN(B2:G2) =H2/H$2
3 Picard, J. 20 18 19 20 17 18 =SUM(B3:G3)-MIN(B3:G3) =H3/H$2
4 Riker, W. 18 17 16 18 17 20 =SUM(B4:G4)-MIN(B4:G4) =H4/H$2
5 Troi, D. 20 20 19 20 20 19 =SUM(B5:G5)-MIN(B5:G5) =H5/H$2

As more scores are added, the max points will be updated, and the percentages easier to recalculate.

Top of Page

Last Update: May 17, 2011