B
Bert
I've designed a simple spreadsheet to track grades using Excel 2003.
Each specific assignment/quiz grade for a student is in a separate column.
The beginning row of each class contains the highest grades possible for
each assignment/test/etc.
Sample:
Name Rank Cumulative Offset Gr1 Gr2
G3 G4.
1 Possible Score 260
100 20 40 100
2 Student 1 196 40
80 20 E 96
3 Student 3 206
86 20 30 90
4 Student 2 190 60
90 E E 100
..
To determine the grade for each student, I've created a formula (in the
"Rank" Column) that calculates a percentage of the cumulative highest score
possible.
This works fine, except sometimes I excuse a student from an assignment. To
compare their total scores to the cumulative highest score possible would be
unfair, so to accommodate for this, I've added a column labeled "Offset".
For each student in this situation, I put an "E" (for "excused") in
appropriate column. Then I manually put the corresponding high score in the
Offset column for any student who has been excused from a given assignment.
Here's the formula I'm using: =ROUND(((100*C2)/($C$1-D2)),0) ----- Where
C2 is the total of all scores for a given student, $C$1 is the cumulative
highest score possible, and D2 is the offset value if any. (Finally I use
VLOOKUP to insert a letter grade in another column.)
QUESTION:
Is there a way to accommodate this "Excused" situation with a some sort of
conditional formula so I don't have to manually assign an "Offset" for those
students. (maybe with SUMIF?) For example: If "E" is a student's score
for a given assignment, then subtract the highest possible score for that
assignment. Or if a cell is not "E" then include the highest possible score.
Any suggestions would be greatly appreciated.
Thanks!
Each specific assignment/quiz grade for a student is in a separate column.
The beginning row of each class contains the highest grades possible for
each assignment/test/etc.
Sample:
Name Rank Cumulative Offset Gr1 Gr2
G3 G4.
1 Possible Score 260
100 20 40 100
2 Student 1 196 40
80 20 E 96
3 Student 3 206
86 20 30 90
4 Student 2 190 60
90 E E 100
..
To determine the grade for each student, I've created a formula (in the
"Rank" Column) that calculates a percentage of the cumulative highest score
possible.
This works fine, except sometimes I excuse a student from an assignment. To
compare their total scores to the cumulative highest score possible would be
unfair, so to accommodate for this, I've added a column labeled "Offset".
For each student in this situation, I put an "E" (for "excused") in
appropriate column. Then I manually put the corresponding high score in the
Offset column for any student who has been excused from a given assignment.
Here's the formula I'm using: =ROUND(((100*C2)/($C$1-D2)),0) ----- Where
C2 is the total of all scores for a given student, $C$1 is the cumulative
highest score possible, and D2 is the offset value if any. (Finally I use
VLOOKUP to insert a letter grade in another column.)
QUESTION:
Is there a way to accommodate this "Excused" situation with a some sort of
conditional formula so I don't have to manually assign an "Offset" for those
students. (maybe with SUMIF?) For example: If "E" is a student's score
for a given assignment, then subtract the highest possible score for that
assignment. Or if a cell is not "E" then include the highest possible score.
Any suggestions would be greatly appreciated.
Thanks!