Error With % Complete Child Roll-up caculation

Katye Reed

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 % Complete is calculated by averaging % Complete for Task 1.1 and Task 1.2

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

 

 

 

Paul Newcome

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.

Katye Reed

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

 

 

Katye Reed

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

 

Paul Newcome

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

 

Try putting the following in the parent row only:

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

Katye Reed

Thank you!