Div/0 error, ignoring blanks on weighted averages

[CRGWRD]

Hey Guys, First post.

Looking for some help. As the title says I'm working on a formula that does weighted averages while ignoring blanks, the formula works fine until I clear the cells and then I'm left with a Div/0! error.

I have this so far:

=SUMPRODUCT(C12:U12,$C$10:$U$10)/SUMPRODUCT(--(C12:U12<>""),$C$10:$U$10)


The weight of the assignments is in C:10 to U:10 and the marks are in C:12 to U:12

 

[joeu2004]

Simplest:

=IFERROR(SUMPRODUCT(C12:U12,$C$10:$U$10)/SUMPRODUCT(--(C12:U12<>""),$C$10:$U$10), "")

Errata.... Ignore the suggested alternative (SUMIF). It works if C12:U12 are empty (no value). But not if they are the null string ("").

 

[NdNoviceHlp]

=IF(ISERROR(SUMPRODUCT(C12:U12,$C$10:$U$10)/SUMPRODUCT(--(C12:U12<>""),$C$10:$U$10)),"ERROR",SUMPRODUCT(C12:U12,$C$10:$U$10)/SUMPRODUCT(--(C12:U12<>""),$C$10:$U$10))

This might be of interest...
https://www.mrexcel.com/forum/excel-questions/52566-removing-div-0-my-sheet.html?highlight=error
HTH. Dave