I am looking to create a formula in Excel to sum a number of grades on student assignments. Here is the relevant info:
There were 11 assignments total, with grades entered into 11 columns (Columns G-Q).
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).
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.
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).
"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"."
Here is what I have so far:
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.