Error With % Complete Child Roll-up caculation

✭✭✭✭✭
edited 12/09/19

Hello,

My team has requested that we use an average % complete of the child cells to populate the parent cell. I am struggling to get this to work. Does it matter if you are rolling up % complete that have already been used in a average formula?

For example

Task 1.1 % Complete is calculated by the child cells 1.1.1 and 1.1.2.

Can I use this average child formula if there is only 1 child cell? Anyone know the workaround for this?

Thanks so much for the assistance!

R/S,

Katye

• ✭✭✭✭✭✭

You should be able to use =AVG(CHILDREN)) regardless of how many children rows you have, even just 1. It should also work on all parent rows regardless of hierarchy.

If you have tried it, and it is still not working, go ahead and post a screenshot, so we can maybe help you a little better.

• ✭✭✭✭✭

Hi Paul -

Thanks for reaching out and providing assistance. I was finally able to have this work. I believe it was an error on my part when inputting the formula.

Have a great day!

Katye

• ✭✭✭✭✭

Hi Paul,

So I find this very strange, sometimes when I use the formula I receive a Divide By Zero error while other times I do not. I believe the formula I am using (see below) is correct. I'm not sure why I still receive this message? My column appears to be set a %.

=AVG(CHILDREN([% Complete]11:[% Complete]18))

Any help would be greatly appreciated!

Thanks,

Katye

• ✭✭✭✭✭✭

It should simply be =AVG(CHILDREN([% Complete]parent row))

Try putting the following in the parent row only:

=AVG(CHILDREN([% Complete]@row)

• ✭✭✭✭✭

Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!