Hi All,
I have a testing sheet which has Test Scenarios (Level 0 -> Grandparent), test cases (Level 1 -> Parent) and test steps (Level 2 -> children). I am trying to formulate the status for test scenarios and test cases based on the status of all the test steps.
My current formula is stated below. It is working perfectly for the test case (Level 1). However is calculating completely wrong for test scenarios (Level 0). My guess is because of blank values in dark and light blue rows in the test result column. The Test result column is a drop down with possible values - Passed, In Progress, Failed, Blocked, Not started.
Can somebody please help? Also pasted a snippet of my sheet below, if it helps.
Current formula -
=IF(Level@row = 1, IF(COUNTIFS(CHILDREN([Test Result]@row), "Blocked") > 0, "Blocked", IF(COUNTIFS(CHILDREN([Test Result]@row), "Failed") > 0, "Failed", IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started")), "In Progress")))))), IF(Level@row = 0, IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN(), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started", @cell = "Blocked", @cell = "Failed")), "In Progress"))))))
