Divide By Zero Error with Average

Barent Wagar

Hi,

I'm trying to set up a formula that averages the scores for four columns, but doesn't bring up the Divide by Zero error if the scores haven't been entered yet (either leaves blank or puts a 0).

The Average formula works fine but I can't get the IFERROR part to work.

Current formula: =ROUND(AVG([LOR 1]1:[LOR 4]1), 1)

Thanks

Heather Duff

Hi @Barent Wagar ,

Try this:

=IFERROR(ROUND(AVG([LOR 1]1:[LOR 4]1), 1),"")

This should replace the error message with a blank.

Let me know if it works!

Best,

Heather

Paul Newcome

Try this...

=IFERROR(ROUND(AVG([LOR 1]1:[LOR 4]1), 1), "")

Kariv

I am having the same issue, trying to average 5 rows in a metrics sheet where some of them contain #DIVIDE BY ZERO. I am using this formula, but it doesn't return any value, not even an error msg. If anyone can help, it would be much appreciated!

=IFERROR(AVG([Column2]22:[Column2]26), "")

Paul Newcome

@Kariv You need to get the error removed from the cells you are referencing.

Kariv

@Paul Newcome ohhhhh that makes a lot of sense now! Thank you so much!

Paul Newcome

Happy to help. 👍️

Helena P.

HI Paul,

would you be able to help. I have similar problem. I am trying to do SUMIf with Iferror formula as otherwise it brings back the divide by zero error. Some cells are blank as no data was populated.

i managed to build the formula but i get no sum of remaining cells that contain values.

Please see the formula below.

=IFERROR(SUMIF(Items:Items, "Design", [29/04/24]:[30/12/24]), "")

would you be able to help?

Paul Newcome

@Helena P. Are you able to provide some screenshots for context? SUMIF doesn't normally throw that particular error.

Helena P.

hi @Paul Newcome

I wonder if the range was the issue as I tried to reference the entire sheet and the fact that a lot cells still dont have any value.

I actually managed to solve this by adding another column to do the calculation for me per row, and then I used this formula in Sheet summary fields and I got the values back :)

=IFERROR(SUMIF(Items:Items, "Design", [Total per Task]:[Total per Task]), "")