Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally summing cells based on conditions in other rows
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally summing cells based on conditions in other rows
Your examples are a little hard to follow because of the layout in the forum. Any chance you can link a spreadsheet example -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=553157 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally summing cells based on conditions in other rows
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" wrote in message ... Your examples are a little hard to follow because of the layout in the forum. Any chance you can link a spreadsheet example -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=553157 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally summing cells based on conditions in other rows
I tried to send you my email address via Private Messaging but it seems you have this disabled. Matt -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=553157 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing rows based on column headings | Excel Worksheet Functions | |||
List rows in an order based on a cells value | Excel Discussion (Misc queries) | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions | |||
locking cells based on results at runtime | Excel Worksheet Functions | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |