#DIVIDE BY ZERO Error Resolution Attempt
Good afternoon! I am trying to replace the #DIVIDE BY ZERO to ready "Update the tasks first!" so I thought =IFERROR(AVG(CHILDREN()) <> 0, 100/ AVG(CHILDREN()), "") would do it. What is the syntax for this please?
Thank you!
Best Answers
-
Hi @Chris Hallo
Hope you are fine, please try the following formula:
=IFERROR(IF(AVG(CHILDREN()) <> 0, 100 / AVG(CHILDREN()), "Update the tasks first!"), "")
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
You are welcome, i will be happy to help you any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hey there Chris,
I think you may have made it too complex. Correct me if I'm wrong, but you're just looking to get the average percent of tasks completed onto a Parent cell of all its children, right?
I think this would work for you:
=IFERROR(AVG(CHILDREN()), "")
Right now you're getting "#Divide by Zero" because Smartsheet recognizes the blank values as 0%. If they're all 0, it causes that error.
Just a heads up though, you could have this populate automatically if you use the "% Complete column" field option in Project Settings. Right-click any column and select "Project Settings". Then make sure you're on "Dependency Settings" -> Options -> % Complete column:
If you change that to your "Completion %" column, Smartsheet will calculate this for you on all "Parent" cells. It's a weighted average against the Duration field, so you will need to have Start and End Dates for it to calculate properly. Here's Smartsheet's documentation on it: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors | Smartsheet Learning Center
If you're not planning on having dates in fields, stick with the solution with the formula I gave you above.
Let me know how it goes!
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
Hi @Chris Hallo
Hope you are fine, please try the following formula:
=IFERROR(IF(AVG(CHILDREN()) <> 0, 100 / AVG(CHILDREN()), "Update the tasks first!"), "")
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you, Brett. Bassam, this is exactly what I was trying to accomplish. I appreciate both of your time :).
-
You are welcome, i will be happy to help you any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!