Excel Formulas
Description
The intent of this brief document is to help a person who has some experience using
Microsoft Excel to create a grade sheet. If you have no prior experience with Excel at all,
you would benefit from spending a few minutes with someone who has.
Introduction
workbook – entire Excel document
worksheet – one page of a workbook
you might use a workbook for a school year, and each class is a worksheet
cell – intersection of column and row
name: column letter and row number (A4, C7)
A cell may contain:
1. text Exam 1
2. value 87
3. formula =A5+C8
4. function =SUM(A5:A10)
copy cells – click cell, then drag “fill handle” (lower right of active cell)
formulas automatically adjust
this is useful to copy grade calculations from one student to others
Useful Formulas and Functions
Suppose you have 10 cells, A1 through A10, for quizzes. In cell A12 you have:
1. =SUM(A1:A10) The total for all 10 quizzes
2. =AVERAGE(A1:A10) The average of all 10 quizzes
3. =MIN(A1:A10) The smallest of the 10 quizzes
4. =COUNT(A1:A10) The number of scores recorded so far
Combining a few of these, we can accommodate dropping the lowest quiz:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)
A Complete Class
You could use a row for each student. After creating the necessary formulas for one
student, copy the formulas (using previous instructions) to all of the other students’ rows.
Examples
Method 1 – Percentage System
3 tests 70% of grade
3 quizzes 30% of grade
A B C D E F G H I J K
Name T1 T2 T3 Q1 Q2 Q3 Grade
John 98 87 89 82 89 95 =AVERAGE(B3:D3)*0.7+AVERAGE(G3:I3)*0.3
Mary 82 90 76 90 87 67 =AVERAGE(B4:D4)*0.7+AVERAGE(G4:I4)*0.3
Susan 62 71 65 48 78 56 =AVERAGE(B5:D5)*0.7+AVERAGE(G5:I5)*0.3
Because we’re using percentages here, if you excuse a student from a quiz, you just leave
it blank. If you want to assign a zero, place a zero instead of leaving it blank.
Example 2 – Point System
3 exams 100 points each
3 assignments points vary per assignment
A B C D E F G H I J K
Name T1 T2 T3 Q1 Q2 Q3 Grade
John 98 87 89 15 40 30 =SUM(B3:I3)/SUM($B$7:$I$7)*100
Mary 82 90 76 15 35 35 =SUM(B4:I4)/SUM($B$7:$I$7)*100
Susan 62 71 65 12 30 25 =SUM(B5:I5)/SUM($B$7:$I$7)*100
Max Possible 100 100 100 20 40 35
Since we’re dealing with points in this example, there is a separate row storing the
maximum possible points. The dollar signs in the formulas are there so that when you
copy the formula from one student to others, row 7 remains constant. This is the row that
contains the maximum possible points regardless of which student we are computing a
grade for. Also note that with a point system, if you excuse a student from a quiz, you
need to modify that particular student’s formula.