L
Lewis Clark
Good Evening, All:
I have a gradebook that covers a 9 week term. Each student is on a separate row, and there are 25 students. The 6 columns for each week cover the 5 graded assignments each week and the weekly average.
For example:
- Columns D through H are for the 5 graded assignments in Week 1.
- Column I holds the weekly average for Week 1.
- Column J is a blank spacer column that separates Week 1 from Week 2.
Similarly, Week 2 covers columns K through P, with a blank spacer in Column Q. And so on in the same pattern for 9 weeks.
In each column, Row 2 holds the maximum number of points for that assignment. The student records are in rows 3 through 27.
I want to be able to calculate the maximum number of points a student could have earned, based on the assignments that have grades entered. I am currently doing it by adding 9 "sumif" functions to handle the 9 distinct grade ranges (1 range for each week). Each "sumif" call looks at the cells in the current row and adds the value of the assignment (from row 2 of that column) if a grade has been entered (when the cell value is greater than or equal to zero). This works, but the formula is very long. Is there a more elegant way to calculate this?
I tried using named ranges, but got the #value error. I think this was because one of the grade columns for each week has an "IF" formula call to another worksheet in the same workbook. The IF function enters either a blank value ("") or a number for the grade. The other columns for grades are all entered manually.
The weekly average columns seem to complicate things - without them I could use one simple "sumif" call. But I'd prefer not to move them or delete the weekly averages.
Thanks in advance for any assistance.
I have a gradebook that covers a 9 week term. Each student is on a separate row, and there are 25 students. The 6 columns for each week cover the 5 graded assignments each week and the weekly average.
For example:
- Columns D through H are for the 5 graded assignments in Week 1.
- Column I holds the weekly average for Week 1.
- Column J is a blank spacer column that separates Week 1 from Week 2.
Similarly, Week 2 covers columns K through P, with a blank spacer in Column Q. And so on in the same pattern for 9 weeks.
In each column, Row 2 holds the maximum number of points for that assignment. The student records are in rows 3 through 27.
I want to be able to calculate the maximum number of points a student could have earned, based on the assignments that have grades entered. I am currently doing it by adding 9 "sumif" functions to handle the 9 distinct grade ranges (1 range for each week). Each "sumif" call looks at the cells in the current row and adds the value of the assignment (from row 2 of that column) if a grade has been entered (when the cell value is greater than or equal to zero). This works, but the formula is very long. Is there a more elegant way to calculate this?
I tried using named ranges, but got the #value error. I think this was because one of the grade columns for each week has an "IF" formula call to another worksheet in the same workbook. The IF function enters either a blank value ("") or a number for the grade. The other columns for grades are all entered manually.
The weekly average columns seem to complicate things - without them I could use one simple "sumif" call. But I'd prefer not to move them or delete the weekly averages.
Thanks in advance for any assistance.