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 lea