L
Lewis Clark
Hello, All!
I am working on a gradebook. The final grade consists of a homework average, a project average, and three exam grades.
The homework average is calculated in column AV and the project average is calculated in column BF. The raw exam scores are entered in columns BR, BS and BT.
For the homework and project averages, it is important to differentiate between the case where no assignments have been graded, and the case where assignments have earned a grade of zero. When I calculate the homework average, I use the following array formula:
=IF(COUNT(AF10:AU10)=0,"",100*SUM(AF10:AU10/$AF$3:$AU$3)/COUNT(AF10:AU10))
If the count = 0, then no homework grades have been entered and I want nothing to display in the average column (AV). If count is greater than zero, the average is calculated and converted to a number between 0 and 100. The project average uses a similar formula.
I have a summary section in columns BY to CC, where BY is the homework average, BZ is the project average, and CA - CC are the exam scores
In the homework summary column, I would like the cell blank if no homework has been turned in, and use the formula: =IF(ISNUMBER(AV10), AV10, "")
I use similar formulas for the project average summary column and the grade summary columns. The above works fine for displaying the category averages.
My problem occurs when I try to calculate the weighted average for each student. The category weights are in row 3. I’m trying to use the formula: =SUMPRODUCT( (BY10:CC10) * (BY3:CC3) )
This formula gives me a VALUE error. Using the Evaluate Formula tool (Excel 2003) I discovered that when the cells are blank (due to the “” entry in the IF formulas) Excel wants to treat those cells as text even though I have them formatted as numbers. I've tried a variety of array formulas using the ISNUMBER function, but can't get rid of the VALUE errors.
I would be very grateful for any advice the group can offer. Thank you in advance for any assistance.
--
I am working on a gradebook. The final grade consists of a homework average, a project average, and three exam grades.
The homework average is calculated in column AV and the project average is calculated in column BF. The raw exam scores are entered in columns BR, BS and BT.
For the homework and project averages, it is important to differentiate between the case where no assignments have been graded, and the case where assignments have earned a grade of zero. When I calculate the homework average, I use the following array formula:
=IF(COUNT(AF10:AU10)=0,"",100*SUM(AF10:AU10/$AF$3:$AU$3)/COUNT(AF10:AU10))
If the count = 0, then no homework grades have been entered and I want nothing to display in the average column (AV). If count is greater than zero, the average is calculated and converted to a number between 0 and 100. The project average uses a similar formula.
I have a summary section in columns BY to CC, where BY is the homework average, BZ is the project average, and CA - CC are the exam scores
In the homework summary column, I would like the cell blank if no homework has been turned in, and use the formula: =IF(ISNUMBER(AV10), AV10, "")
I use similar formulas for the project average summary column and the grade summary columns. The above works fine for displaying the category averages.
My problem occurs when I try to calculate the weighted average for each student. The category weights are in row 3. I’m trying to use the formula: =SUMPRODUCT( (BY10:CC10) * (BY3:CC3) )
This formula gives me a VALUE error. Using the Evaluate Formula tool (Excel 2003) I discovered that when the cells are blank (due to the “” entry in the IF formulas) Excel wants to treat those cells as text even though I have them formatted as numbers. I've tried a variety of array formulas using the ISNUMBER function, but can't get rid of the VALUE errors.
I would be very grateful for any advice the group can offer. Thank you in advance for any assistance.
--