SUMPRODUCT Value Error Question

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




--

 

[ViMuTo]

This might work in your situation:

=SUMPRODUCT(--(BY10:CC10),(BY3:CC3) )

 

[ViMuTo]

This might work in your situation:

=SUMPRODUCT(--(BY10:CC10),(BY3:CC3) )

 

[Lewis Clark]

The cells in the range BY10:CC10 that are blank (due to the "" in the IF formulas) still have VALUE errors.

Thank you for the suggestion.

--


This might work in your situation:

=SUMPRODUCT(--(BY10:CC10),(BY3:CC3) )

 

[Bob Phillips]

It looks to me that you have a space in one of the fields, if it were blank
SUMPRODUCT would not complain.

BTW, you only need a formula of

=SUMPRODUCT(BY10:CC10,BY3:CC3)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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.




--

 

[Lewis Clark]

I verified there are no spaces between the double quotes in any of the formulas. Are there any other places where the spaces might be critical?

Thanks for the suggestion.

--

It looks to me that you have a space in one of the fields, if it were blank
SUMPRODUCT would not complain.

BTW, you only need a formula of

=SUMPRODUCT(BY10:CC10,BY3:CC3)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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.




--

 

[Peo Sjoblom]

If you are using

SUMPRODUCT( (BY10:CC10) * (BY3:CC3) )

you will get errors if there are blanks but if you use the built in method
in SUMPRODUCT

=SUMPRODUCT(BY10:CC10,BY3:CC3)

then there is no way on earth you can get a value error since it ignores
text, the only way would be if you already have a value error in the range


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




I verified there are no spaces between the double quotes in any of the
formulas. Are there any other places where the spaces might be critical?

Thanks for the suggestion.

--

It looks to me that you have a space in one of the fields, if it were blank
SUMPRODUCT would not complain.

BTW, you only need a formula of

=SUMPRODUCT(BY10:CC10,BY3:CC3)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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.




--

 

[Dave Peterson]

This is a text based newsgroup. Most people post in plain text.

Try this array formula:

=SUM(IF(ISNUMBER(BY10:CC10),BY10:CC10*BY3:CC3))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column (if you ever
transpose your data).

 

[Lewis Clark]

I did get this to work as Peo described. I thought I had tried that syntax unsuccessfully before, but it works now. Must be getting old.

Thank you very much to all who responded!

--

If you are using

SUMPRODUCT( (BY10:CC10) * (BY3:CC3) )

you will get errors if there are blanks but if you use the built in method
in SUMPRODUCT

=SUMPRODUCT(BY10:CC10,BY3:CC3)

then there is no way on earth you can get a value error since it ignores
text, the only way would be if you already have a value error in the range


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




I verified there are no spaces between the double quotes in any of the
formulas. Are there any other places where the spaces might be critical?

Thanks for the suggestion.

--

It looks to me that you have a space in one of the fields, if it were blank
SUMPRODUCT would not complain.

BTW, you only need a formula of

=SUMPRODUCT(BY10:CC10,BY3:CC3)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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.




--