Average children with IF
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
Answers
-
-
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
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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..
-
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.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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....
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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 ?
-
Please post here and @ me if you find an automatic way to do this! Good luck!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!