#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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    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"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/03/21 Answer ✓

    @Chris Hallo 

    You are welcome, i will be happy to help you any time.

    PMP Certified

    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

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    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!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    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"

  • Chris Hallo
    Chris Hallo ✭✭✭✭

    Thank you, Brett. Bassam, this is exactly what I was trying to accomplish. I appreciate both of your time :).

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/03/21 Answer ✓

    @Chris Hallo 

    You are welcome, i will be happy to help you any time.

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!