Using `SUMPRODUCT` without dividing by zero

Question

See the image above for my hypothetical grade sheet in Libre Calc. There are two rows. One contains the points achieved for each assignment while the second records the points available. I would like to keep a running average of the grades, but I do not wish to update the formula for the running average each time a new assignment is added. Currently, I am using:

SUMPRODUCT( ENTIRE_POINTS_ACHIEVED_ROW, 1 / (ENTIRE_POINTS_AVAILABLE_ROW) )

I then find the average by dividing by the number of non-blank rows, using COUNTA().

As you can see above, assignments 1-3 are complete, but there is no information for the fourth. Because of this, I get a divide by zero error. How can I accommodate for this? Or, is there a more straightforward way to achieve what I've described above?

Answer 1

As Scott Craner proposed , using an Array formula is the way to follow. With LO Calc, the following formula should work (enter it with CTRL+SHIFT+ENTER to make it an array formula):

=SUMPRODUCT(IF(B2:E2<>0; B2:E2; "");1/B3:E3)/COUNT(B2:E2)*100

^{I've adapted an example from the OpenOffice.org Calc documentation for this.}