Elegant Formula Help Desired

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

 

[Ron Rosenfeld]

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 don't know about "elegant" but:

1. If I understand you correctly, your information runs from Column D throught
Column BL (BM is an average column for Week 9)

2. I assume that there is no numeric data in row 2 of the Average column. It
could be blank, or contain text.

3. That being the case, the following **array-entered** formula should work
for Row 3 -- and can be copied/dragged down as far as needed.

To **array-enter** a formula, after typing/copying it into the cell, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula:

=SUM(IF(ISNUMBER($D$2:$BL$2),$D$2:$BL$2*ISNUMBER(D3:BL3)))

If Row 2 of the Average column does have numeric data, than a somewhat more
convoluted formula would be needed to ignore that column.


--ron

 

[Ron Rosenfeld]

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?

This is another formula that works without regard to the contents of the
Average column Row 2. It is also an **array-entered** formula:

=SUM(IF(MOD(COLUMN($D$2:$BL$2)-4,7)<5,$D$2:$BL$2*ISNUMBER(D3:BL3)))


--ron

 

[Lewis Clark]

Ron,

This is fantastic! And it more than meets the "elegant" criteria: when the pointer is on that cell, the formula bar now has one line of characters instead of 3 lines. Much cleaner than my 9 "sumif" calls.

I used this version because there is data in the average column of Row 2 (total points possible for the week). I could have easily done without this data if necessary as it is also listed elsewhere.

I have never used the "isnumber" function before, so between that and studying the logic of this formula you taught me some neat tricks.

Thank you very much!!

--

This is another formula that works without regard to the contents of the
Average column Row 2. It is also an **array-entered** formula:

=SUM(IF(MOD(COLUMN($D$2:$BL$2)-4,7)<5,$D$2:$BL$2*ISNUMBER(D3:BL3)))


--ron

 

[Ron Rosenfeld]

Ron,

This is fantastic! And it more than meets the "elegant" criteria: when the pointer is on that cell, the formula bar now has one line of characters instead of 3 lines. Much cleaner than my 9 "sumif" calls.

I used this version because there is data in the average column of Row 2 (total points possible for the week). I could have easily done without this data if necessary as it is also listed elsewhere.

I have never used the "isnumber" function before, so between that and studying the logic of this formula you taught me some neat tricks.

Thank you very much!!

You're very welcome. Glad to help and thanks for the feedback.


--ron