IF Match formula failing. Can you use Children as the test to search?

MTaylor

=IF(FIND("Fail", (CHILDREN([Subtask Status]231))) > 0, "Failed Subtask", IF(Column231 = 1, "Pass", IF(Column231 = 0, "Not Started", IF(Column231 > 0, "Review In Progress", IF(Column231 < 0, "Review in Progress")))))

The above formula is returning #Invalid Data Type

I also tried to break the formula down into two parts to test each part of =IF(FIND("Fail", (CHILDREN([Subtask Status]231))) > 0, "Failed Subtask", "No") results is #Invalid Data type

=IF(Column231 = 1, "Pass", IF(Column231 = 0, "Not Started", IF(Column231 > 0, "Review In Progress", IF(Column231 < 0, "Failed Subtask")))) results are correct

 

Desired outcome

IF "Fail" is in the children of Subtask Status231 return "Failed Subtask" otherwise IF Column231=0 return Not started, IF Column231>0 return In Progress, IF column231<0 return Failed Subtask

 

Screen grab shows the two scenarios and the reason I need to add the Find function to the calculation

 

MarkLindsey

This is basically the same as your previous question.  It looks like you've dropped the "Pass" checkbox column, but you can still get the results you want using the following.

For the "Column" formula, use:

=(COUNTIF(CHILDREN([Subtask Status]10), "Pass")) / (COUNT(CHILDREN([Subtask Status]10)))

For the "Status" Column, use:

=IF(COUNTIF(CHILDREN([Subtask Status]10), ="Fail") > 0, "Failed Subtask", IF(Column10 = 1, "Passed", IF(Column10 = 0, "Not Started", "Review In Progress")))

MarkLindsey

One correction on the "Column" formula.  You need to divide by the children count of your "Test Type" column.  The correct formula would be:

=(COUNTIF(CHILDREN([Subtask Status]10), "Pass")) / (COUNT(CHILDREN([Test Type]10)))