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 2. value 3. formula 4. function Exam 1 87 =A5+C8 =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 3 quizzes A
Name John Mary Susan
70% of grade 30% of grade B C D E
T1 T2 T3 98 87 89 82 90 76 62 71 65
F G H I
Q1 Q2 Q3 82 89 95 90 87 67 48 78 56
J
K
Grade =AVERAGE(B3:D3)*0.7+AVERAGE(G3:I3)*0.3 =AVERAGE(B4:D4)*0.7+AVERAGE(G4:I4)*0.3 =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 3 assignments A
Name John Mary Susan
100 points each points vary per assignment B
T1 98 82 62
C
T2 87 90 71
D
T3 89 76 65
E F G H I
Q1 Q2 Q3 15 40 30 15 35 35 12 30 25 20 40 35
J
K
Grade =SUM(B3:I3)/SUM($B$7:$I$7)*100 =SUM(B4:I4)/SUM($B$7:$I$7)*100 =SUM(B5:I5)/SUM($B$7:$I$7)*100
Max Possible 100 100 100
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.