# Divide By Zero Error with Average

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭
Options

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), "")

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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]), "")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!