AVG % Complete with variables

I have turned off % Complete in the project settings and all Children "% Complete" values are manual input. This has to be done because I have a section on the project sheet that cannot be auto-calculated.

I have used the =AVG(CHILDREN()) formula for the Parent (light blue) sections. If no value has been input to "% Complete" it returns "DIVIDE BY ZERO". How do I have it return a zero or blank value until any input has been made on the Children rows?

Also, one of the parents will be a manual percentage calculation without a formula (AHJ approved set). How do I best calculate the "% Complete" for the ANCESTOR (dark blue)?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!