Elegant Formula Help Desired

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.

On Mon, 05 Dec 2005 04:00:26 GMT, "Lewis Clark" <lewis_clark_644 @ yahoo.com>
wrote:

>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

On Mon, 05 Dec 2005 04:00:26 GMT, "Lewis Clark" <lewis_clark_644 @ yahoo.com>
wrote:

>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

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

On Mon, 05 Dec 2005 11:27:24 GMT, "Lewis Clark" <lewis_clark_644 @ yahoo.com>
wrote:

>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