CODEWITHSUNDEEP
excellibreoffice-calc
Matt Hancock
Matt Hancock

Reputation: 4039

Using `SUMPRODUCT` without dividing by zero

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?

Upvotes: 2

Views: 2206

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Use this array formula instead:

=SUM(IF($B$3:$E$3<>"",B2:E2/B3:E3))/COUNT(B2:E2)

Being an array it must be confirmed with Ctrl-Shift-Enter. If done properly {} will be put around the formula.

This is excel, It may not work in LibreOffice.

enter image description here

Upvotes: 2

tohuwawohu
tohuwawohu

Reputation: 13618

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

enter image description here

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

Upvotes: 4

Related Questions