Conditionally summing cells based on conditions in other rows

[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!

 

[Mallycat]

Your examples are a little hard to follow because of the layout in the
forum. Any chance you can link a spreadsheet example

 

[Bert]

Yes, I see the formatting problem. I'm sorry, but the spreadsheeet isn't
online so I can't send a link to it. Does the newsgroup accept attachments?
I'd be happy to send it to an individual email address. Failing that I
could try to send a more "compressed" version of the example.
Bert

 

[Mallycat]

I tried to send you my email address via Private Messaging but it seems
you have this disabled.

Matt