Forum Discussion

Maj786's avatar
Maj786
Copper Contributor
Feb 25, 2024

Help with an Excel Formula

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 :sad:

 

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 

PeterBartholomew1 

SergeiBaklan 

djclements 

  • 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.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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, ""))
    )

     

  • 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.

     

     

  • 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")

    • Maj786's avatar
      Maj786
      Copper Contributor

      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

      • 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.

Resources