Help with an Excel Formula

[Maj786]

Hello all, 

First time poster here.

I need help on creating a formula on excel for the scenario below, please help as im really struggling 

Scenario: 

In sheet 1, I have a scenario (column B13:B15) and corresponding steps status (column E13:E15) and depending on the status of those steps in column E13:E15 I have to determine the final test case status column F13:F15 and provide a status report (see the colourful table at the top of the sheet).

Conditions:

• if all the steps in column E13:E15 are passed-->it is considered as PASS in column F13:F15
• if at least one step in column E13:E15 is failed-->it is considered as fail test case in column F13:F15 = FAIL
• if there are combination of Pass and In progress steps-->It is considered as IN PROGRESS status in in column F13:F15
• if there are combination of Pass and Blocked steps-->It is considered as blocked status
• if there are combination of Pass and Failed steps-->It is considered as FAIL status
• If all the steps are in No Run status --> Test Case status = No Run
• if there are combination of Pass and No Run steps-->It is considered as IN PROGRESS status in in column F13:F15

Ive uploaded a picture of my sheet 

• 

@Patrick2788

@nixinjector 

@HansVogelaar 

@NikolinoDE 

@PeterBartholomew 

@SergeiBaklan 

@djclements 

[#2]

@Maj786 

Please test carefully:

=IFS(COUNTIFS(E13:E15, "Pass")=3, "Pass", COUNTIFS(E13:E15, "Fail")>0, "Fail", COUNTIFS(E13:E15, "Not Run")=3, "Not Run", COUNTIFS(E13:E15, "Blocked")>0, "Blocked", TRUE, "In Progress")

[#3]

@Maj786 

This assumes the pattern of 3 rows and 2 columns per scenario persists throughout the entire dataset.

= LET(
    statusRow, WRAPROWS(TOROW(statusBlock, 1), 4),
    scenarioStatus, BYROW(statusRow, LAMBDA(s,
      IFS(
        AND(s="PASS"), "PASS",
        OR(s="FAIL"), "FAIL",
        AND(s = "Not Run"), "Not Run",
        OR(s = "BLOCKED"), "Blocked",
        TRUE, "IN PROGRESS"
      )
    )),
    scenarioStatus
  )

From there you could populate your summary table or distribute the results to display them along with the scenario records.

[#4]

@HansVogelaar 

Thank you for this! Ever so helpful. 

What about if i had: 

• one step that was out of scope and two steps Passed --> i would expect that test case to be Pass 
• three steps Out of Scope --> i would expect this to make the overall test case status to be out of scope.

Also the 3 in statement below, is this the number of steps? The only reason i ask is in case i have to increase the number of steps would this need to be reflected here? 

=IFS(COUNTIFS(E13:E15, "Pass")=3

[#5]

@Maj786 

This formula is a little bit longer because of the data arrangement and taking into account a scenario that is different than the ones you've outlined (e.g. the result is not PASS and after UNIQUE is run and any instance of PASS is removed, there are 2 different statuses).  Additionally, my solution does not use merged cells in F.  Merged cells should be avoided because they do not play well with dynamic arrays and the look of merged cells can be achieved with borders and cell formatting.

=LET(
    status_matrix, WRAPROWS(status, 3),
    GetStatus, LAMBDA(current_row,
        LET(
            distinct, UNIQUE(current_row, 1),
            ct, COUNTA(distinct),
            no_pass, FILTER(distinct, distinct <> "PASS", ""),
            mixed, IF(
                COUNTA(no_pass) = 2,
                "Please check status",
                IF(no_pass = "No Run", "IN PROGRESS", no_pass)
            ),
            IF(ct = 1, distinct, mixed)
        )
    ),
    results, BYROW(status_matrix, GetStatus),
    TOCOL(EXPAND(results, , 3, ""))
)

[#6]

@Maj786 

=IFS(COUNTIFS(E13:E15, "Pass")=3, "Pass", COUNTIFS(E13:E15, "Fail")>0, "Fail", COUNTIFS(E13:E15, "Not Run")=3, "Not Run", COUNTIFS(E13:E15, "Blocked")>0, "Blocked", COUNTIF(E13:E15, "Out of Scope")>=2, "Out of Scope", AND(COUNTIF(E13:E15, "Pass")=2, COUNTIF(E13:E15, "Out of Scope")=1), "Pass", TRUE, "In Progress")

And yes, the 3 is the number of steps.

[#7]

@Maj786 

I found this question addressed to me on my email notification though not within the discussion itself.

"What if I had more than 3 rows?"

Since 2016, I have programmed Excel using array formulas only (originally using CSE, which was a pretty dreadful experience when you required flexibility).  Typically a formula I write for 3 rows will also work for 36.  What it will generally not cope with is a varying number of cells per 'scenario'.  It is possible, but it is likely that greater effort will be required to identify the data objects than for the subsequent calculations.