AVG % Complete with variables

Dana S

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)?

AVG % Formula.jpg

Uploaded Mar 3, 2023232.87 KB

Paul Newcome

Wrapping your formula in an IFERROR should do the trick for you:

=IFERROR(AVG(CHILDREN()), 0)

This should also work on any level whether the data being pulled in is calculated via formula or manually entered.

Paul Newcome

Wrapping your formula in an IFERROR should do the trick for you:

=IFERROR(AVG(CHILDREN()), 0)

This should also work on any level whether the data being pulled in is calculated via formula or manually entered.

Dana S

Thank you@Paul Newcome

Paul Newcome

Happy to help. 👍️