I am looking to create a formula in Excel to sum a number of grades on student assignments. Here is the relevant info:

1. There were 11 assignments total, with grades entered into 11 columns (Columns G-Q).

2. When an assignment was submitted the student could have received one of 0, 7, 8.5, or 10. When an assignment wasn't submitted the cell was left blank (there is a "-" in the cell).

3. Only the first 9 assignments submitted should be summed. So, for instance, if the cells in columns G-O are non-empty (i.e., the student received a grade) then the values in the cells in columns P and Q should be ignored.

4. Of these 9 assignments, I need to drop the lowest grade (i.e., I need to subtract the value of the lowest assignment in the group of 9 from the sum of the 9 grades).

For example:

"Suppose a student submitted 10 assignments and received a grade of "7" on assignment 1 and 2, "10" for assignments 3-7, skipped assignment 8 ("-" in cell), and received a "7" for assignments 9-11. The formula I am looking for would return the result "71"."

EDIT:

Here is what I have so far:

=IF(COUNT(G2:Q2)=9,SUM(G2:Q2)-MIN(G2:Q2),IF(COUNT(G2:Q2)<9, SUM(G2:Q2)))


The problem is that this doesn't handle the students who submitted more than 9 assignments. For those students, I need any grades beyond the first 9 in the range to be ignored and not summed. That's what's really tripping me up.

-
I noticed my question was downvoted, is there something I could do to improve the question? –  Dennis Dec 22 '12 at 6:56
I'm not the one that down voted but I would suggest some screenshots instead of just the text. Also an example spreadsheet would be nice. Also there isn't any magical solution to your problem, using excel you're going to have to construct multiple sets of rules. I would try breaking the problem up into steps, and at least demonstrate that you're trying to solve your own problem before asking it here. Just my \$0.02. –  slm Dec 22 '12 at 7:30

This is what I've come up with (use the one that is compatible with your regional settings):

=IF(COUNT(G1:Q1) < 9; NA();
IF(COUNT(G1:Q1) = 9; SUM(G1:Q1) - MIN(G1:Q1);
IF(COUNT(G1:P1) = 9; SUM(G1:P1) - MIN(G1:P1);
SUM(G1:O1) - MIN(G1:O1)
)))

=IF(COUNT(G1:Q1) < 9, NA(),
IF(COUNT(G1:Q1) = 9, SUM(G1:Q1) - MIN(G1:Q1),
IF(COUNT(G1:P1) = 9, SUM(G1:P1) - MIN(G1:P1),
SUM(G1:O1) - MIN(G1:O1)
)))


Here is a sample sheet. Here is a picture of it in action.

If the student submitted less than 9 assignment the formula calls the NA() function. After that the formula narrows the range from the right until there are only 9 values inside it (so the range always starts with G1:).

I hope the formula will work as you expect.

-
Nice answer hegearon! +1 Note: assume Dennis will need to replace your semi-colons with commas (depends on region) –  barry houdini Dec 22 '12 at 16:19
Thanks for the heads-up. –  hegearon Dec 22 '12 at 16:40
@hegearon Brilliant solution. I had worked up a hack that got the job done, but it was ugly and involved adding extra columns. The one change I made was to replace NA() with SUM(G1:Q1) since students who submitted fewer than 9 assignments simply were to have their scores added. That wasn't very clear in my question, though. Thanks! –  Dennis Dec 22 '12 at 19:35