Divide By Zero Error with Average
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
Answers
-
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
-
Try this...
=IFERROR(ROUND(AVG([LOR 1]1:[LOR 4]1), 1), "")
-
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), "")
-
@Kariv You need to get the error removed from the cells you are referencing.
-
@Paul Newcome ohhhhh that makes a lot of sense now! Thank you so much!
-
Happy to help. 👍️
-
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?
-
@Helena P. Are you able to provide some screenshots for context? SUMIF doesn't normally throw that particular error.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!