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!