Calculate Grades
This page will give examples for using different Excel formulas or functions to calculate grades in various grading schemes.
This Page
- How Excel Formulas Work
- Summing Up Scores
- Calculating a Numeric Percentage
- Convert Numeric Percentage to Letter Grades
- Weighting Assignments by Points
- Weighting Assignments by Percentage
- Dropping Lowest Score
- Dropping the Lowest Two (or More) Scores
- Keeping the Highest Score
- Keeping the Highest Two (or More) Scores
- Maintaining a Running Grade
- 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.
- All formulas begin with an equals sign (=).
- Some formulas useoperands 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. - Other formulas refer to different function such as SUM, AVERAGE and others.
For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3. - 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. - 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.
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 assignment. See mockup spreadsheet below.
| 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
- Designate Column H as the "Totals" column. You can fill in a title in Cell A8.
- Move your cursor to Cell H2, the second row in the "Totals" column.
- 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.
- 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.
- From the menu, select Edit » Fill »Down (or Control+D). This will copy the formula in all the cells but change the row numbers.
Here is the mock spreadsheet with relevant formulas for all students.
| 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) |
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
- Insert another row after the Totals column and label it "Percentage". This will be column I.
- 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.
- Highlight all cells in column I with student scores then select Edit » Fill »Down (or Control+D). to the percentage formula in all the cells.
- To change the decimals to percentages, highlight the cells with the percentage formula, then select Format » Cells. Click the Numbers tab and choose Percentage in the left column. You can also select how many decimal places should be displayed.
| 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 oneof these formulas in the first row.
- =SUM(B2:G2)/120 - Directly sums assignments in numerator and divides by total maximum points.
- =SUM(B2:G2)/(6*20) - Includes calculation for maximum number of points in denominator.
See the running grade section for another method of calculating percentages.
Convert Percentages to Letter Grades
This section explains how to use the IF formula to specify a letter grade based on a numeric range. This page will explain a simple "Pass/Fail" formula before moving on to letter grades or you can skip to the final formulas.
Syntax of 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:
- =IF(I2>=70%,"PASS","FAIL")
Note that the desired grades "PASS" and "FAIL" are marked by quotes in the formula to indicate that text should be written into the cell
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".
| 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
- If you use "70", instead of "70%", the passing grade will be calculated as 7000%
- If your numbers are not formatted as percentages, then the formula should be =IF(I2>=.7,"PASS","FAIL").
- If you see "True" or "False" in a cell, then your formula may be missing a condition in the syntax.
- The formula could be rewritten as =IF(I2<70%,"FAIL","PASS")
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
- =IF(I2>=90%,"A",IF(I2>=80%,"B",IF(I2>=70%,"C",IF(I2>=60%,"D","F"))))
Tip: As you add conditions to a nested formula, first add a set of empty parentheses, then fill in the 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
- A = 95 to 100%
- A- = 90 to 94.9%
- B+ = 88 to 89.9%
- B = 83 to 87.9%
- B- = 80 to 82.9%
- C+ = 75 to 79.9%
- C = 70 to 74.9%
- D = 60 to 69.9%
- F = 59.9% and below
- 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 (""). - 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.
| 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 |
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.
| 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.
| 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.
| 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
- Easy to maintain a running total percentage for students.
- Students can easily convert assignment points to a letter grade.
- Formulas easy to construct
Disadvantages
- Requires preplanning to split into points
- Points can be "artificial" (e.g. participation)
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.
| Assignment | Percentage |
|---|---|
| 10 Problem Sets | 45% |
| Midterm | 20% |
| Final Exam | 30% |
| Participation/Attendance | 5% |
To Add Weighted Assignments
- Determine maximum points for each assignments or group of assignments.
- 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).
- 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.
| 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
- Assignments can be given any point value as long as percentages are accurately tracked
- May be beneficial for courses with smaller numbers of assignments.
Disadvantages
- Requires extra columns to calculate each assignment's percentage
- Final formula more complex
- More difficult to track running grades.
Drop the Lowest Score
Many courses with weekly assignments allow students to drop the lowest score.
- Group weekly assignments in adjacent columns (e.g. six assignments in Columns B through G).
- In the next column to the right, insert following formula will add total points and subtract the lowest points
=SUM(B2:G2)-MIN(B2:G2) - 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
| 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 |
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.
- Group weekly assignments in adjacent columns (e.g. six assignments in Columns B through G).
- In the next column to the right, insert 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)) - 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)). - 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 80 (4 x 20), not 120.
See Mock Spreadsheet
| 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 |
Keep the Highest Score
Some courses may allow students to keep the highest score in a set of practice quizzes.
- Group the assignments in adjacent columns (e.g. six assignments in Columns B through G).
- In the next column to the right, insert following formula =MAX(B2:G2).
- Make sure the percentage in column I is calculated based on the maximum number of points for one assignment.
See Mock Spreadsheet
| 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 |
Keep the Highest Two (or More) Score
Some courses may allow students to keep the highest score in a set of practice quizzes.
- Group the assignments in adjacent columns (e.g. six assignments in Columns B through G).
- 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.
- 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). - 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
| 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 |
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.
- 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).
- For each assignment, enter the maximum point value.
- 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.
- When calculating percentages, divide by the values in the Max Points Row.
- 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 formulas is copied through Fill Down, the row reference remains the same.
| 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.
Last Update: May 8, 2006