Average children with IF

Netanel yosef

hi,

i have sheet with ~300 parent row and ~1200 children row.

i try to sum the progress in parent row with IF on his children.

for example : if one of the children row is "sent to fabrication" he get 100% and i have five children row i want to see in parent row the value 20% progress. (1+0+0+0+0)/5

Netanel yosef

@Andrée Starå

Jeff Reisman

Shalom Netanel.

I think you will want to use AVG function in the parent row and reference the children in that column.

In the % column on the parent row, use:

=AVG(CHILDREN([% Column Name]@row))

In my example below, I used =AVG(CHILDREN([Percent Complete]@row)) in the "Percent Complete" column on the parent row. When I add a new child row, the avg updates when I place a value in to the Percent Complete column for the new child row.

(Note: The actual average is 16.6%. Set the decimal places for the column if you don't want it to round to the nearest 1%:)

Regards,

Jeff Reisman

Netanel yosef

thank you for answering.

you right but i want to average only cell with condition. for example average only cell that has a date value in column "submission date to fabrication".

and i need formula for all column "%" and there is in this column children cell and parent cell..

Jeff Reisman

Set your percent field for the child rows to only display 100% for those with a date value in "submission date to fabrication" column.

=IF(ISDATE([Submission Date to Fabrication]@row), 1, 0)

'If Submission Date to Fabrication column on this row is a date value, set the percent complete to 1 (equals 100%) otherwise set it to 0 (equals 0 percent.)

Netanel yosef

In this case I will need to set each children cell this formula that different from parents cell in same culomn.

I have a lot of parents and children in my sheet and also it's all time changed by adding children or make row as parents and add rows to sheet.

I don't want to edit always the formula or copy past...

I search the same formula for all column that know to identify the formula for parents or children automatics....

Jeff Reisman

I ran into this same problem this morning.

The only option I see is that when you create a new child row, do so by copying another child row and then updating the data. That way the formulas copy over. Same thing if you need to add a new parent row - copy an existing parent row with one child row, that way you get everything.

Netanel yosef

yes its an option.

i try to find an automatic way to solve it.

i have another process with this issue..

@Andrée Starå do you have any idea ?

Jeff Reisman

Please post here and @ me if you find an automatic way to do this! Good luck!