If function for children rows

Bas Hoogeveen

Hi all,

I don't know how to build a formula for the following:

I want the parent row to show ''Pass'' when ''Pass'' is filled in for ALL children rows. If one or more of the children rows has ''Fail'' or ''Suspend'', I want the parent row to show ''Fail''. The children rows refer to the children rows of the parent cell.

Can anyone help me out?

Thanks in advance.

Best,

Bas

Giles Magee

Hi Bas,

I am sure someone will come up with a much better answer than this, so perhaps keep an eye out for those solutions.

In the meantime, I couldn't think of a way to do it based on the Pass/Suspend/Fail criteria you mentioned, but a workaround would be to add a helper column where it shows a percent complete, with a Pass being 100% and a Suspend or Fail being 0%, as an example.

Then, you sum the Parent row to equal the percentage of the children rows, and when that hits 100%, the Status of the Parent row changes to "Pass", otherwise it shows Fail.

It would kind of look like this:

The formulas are:

In the Parent Task row of the Status column: =IF(Percentage@row = 1, "Pass", "Fail")

In the Children task rows of the Percentage column: =IF(Status@row = "Pass", 1, 0)

In the Parent Task row of the Percentage column: =SUM(CHILDREN())/COUNT(CHILDREN())

I hope that helps and all makes sense. As I said, there is almost definitely a better, cleaner answer out there, but this might at least give you something to work with in the meantime.

Giles Magee

Hi Bas,

I am sure someone will come up with a much better answer than this, so perhaps keep an eye out for those solutions.

In the meantime, I couldn't think of a way to do it based on the Pass/Suspend/Fail criteria you mentioned, but a workaround would be to add a helper column where it shows a percent complete, with a Pass being 100% and a Suspend or Fail being 0%, as an example.

Then, you sum the Parent row to equal the percentage of the children rows, and when that hits 100%, the Status of the Parent row changes to "Pass", otherwise it shows Fail.

It would kind of look like this:

The formulas are:

In the Parent Task row of the Status column: =IF(Percentage@row = 1, "Pass", "Fail")

In the Children task rows of the Percentage column: =IF(Status@row = "Pass", 1, 0)

In the Parent Task row of the Percentage column: =SUM(CHILDREN())/COUNT(CHILDREN())

I hope that helps and all makes sense. As I said, there is almost definitely a better, cleaner answer out there, but this might at least give you something to work with in the meantime.

Bas Hoogeveen

Thank you for taking the time to come up with a potential solution Giles! It's perfectly clear. I will use this as a workaround for now and keep my eye out for a cleaner solution.