Embedded formula for Parent & Child

jmangus

I am trying to embed a formula for a column (percent completion) for all PARENT rows to show the average of the CHILDREN rows and am having to past the average formula into each parent cell. Is there a way to apply the formula to the entire column to automatically capture averages in the parent row while still being able to manually update the child % completion?

KPH

You can put a formula into the PERCENT COMPLETION column to calculate the average percentage on parent rows only and leave child rows blank. Then you can overtype the cells that are children. However, you have to do it in this order. You cannot enter the data in the child rows and then add a formula to the entire column without overwriting the data that already exists.

The formula you would use is

=IF(COUNT(CHILDREN(ACTIVITY@row)) > 0, AVG(CHILDREN()))

jmangus

This works if I past the formula into each parent row individually. However, I am trying to find a formula that can be embedded into the entire column. It seems that when I select "convert to column formula" it blocks the ability to change any parent or child data in that column.

KPH

To avoid pasting the formula into every cell, you can convert to column formula and convert back to cell formula, or you can use the little cross on the bottom right to drag the formula down the entire column.

If you set the formula as a column formula and leave it as such you cannot overtype it. No formula will enable you to do that. This is a feature of the column formula.

An alternative could be to have another column (in my formula below I call this "new column") in which you type the child percentages. And then adapt the column formula to pull those in on child rows. That way you are typing in one column and displaying the result and the average in another.

=IF(COUNT(CHILDREN(ACTIVITY@row)) > 0, AVG(CHILDREN()),[new column]@row)

I hope this helps.