#DIVIDE BY ZERO Error Resolution Attempt

Chris Hallo

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!

Bassam Khalil

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 Khalil

@Chris Hallo 

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

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!

Bassam Khalil

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:

Chris Hallo

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

Bassam Khalil

@Chris Hallo 

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