Need a Formula that only applies to Parent rows

11/30/21
Answered - Pending Review

I am trying to figure out a formula that will only apply to parent rows. I want the Parent % complete to calculate on the total percentage of all child rows. The embedded formula depends on a duration, and I do not want that.

The current formula I have is this:

=IF((COUNT(CHILDREN([email protected])) > 0),AVG((CHILDREN([% Complete]@row))),([% Complete]@row))

I'm not sure what the second part of my if statement should be, since I want it to be whatever % has been entered for the Child row.

I was hoping to also make this a column formula to apply to all rows, but not sure if that is possible.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I have cleaned up some unnecessary sets of parenthesis to make it easier to read, but it looks like it should be working. If the count of children is greater than zero, average the children, otherwise output the % Complete.


    =IF(COUNT(CHILDREN([email protected])) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

    thinkspi.com

  • Thanks for the response Paul. Unfortunately it is still coming back Unparseable.

  • Hi @Misty Clark

    Is it possible that the column names are spelled wrong? Can you post a screen capture of your sheet, but block out sensitive data? It would be helpful to see the formula open so we can see if the cells are highlighted correctly in different colours.

    Thanks!

    Genevieve

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I agree with @Genevieve P.. First check that the column names in the formula are spelled exactly as the column names in the sheet. If that still isn't it, a screenshot of the column headers as well as a screenshot of the formula in the sheet (similar to below) would be very helpful.



    thinkspi.com

  • =IF(COUNT(CHILDREN([email protected])) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

    Hope this helps!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So you do not have a column called "Primary". Try this...


    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

    thinkspi.com

  • Closer! But that gave me a circular reference warning.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    This will need to go in a separate % Complete column, or you would need to apply an adjusted formula to only the parent rows.

    =AVG(CHILDREN())

    thinkspi.com

  • Hmm, it says Blocked when I tried adding a separate % complete column. I was hoping to have one formula I could use dynamically as a column formula so I don't have to keep copy and pasting the formula every time a project gets added.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try removing it from the original % Complete column. That should clear up the #BLOCKED issue in the second % Complete column.

    thinkspi.com

  • That worked! Thanks so much Paul!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.