Share via

Which formula should I use in Excel to show what the status is of an group of tests

Anonymous
2020-07-22T10:52:19+00:00

Hi, 

I'm creating an overview for test results. 

But I want to have automatically shown what the status is of a group of tests. 

I have on a tab "Configuration" the following possibility of the status where per test can be selected from:

To do

In progress

Passed

Accepted by PO

Failed

Not tested

On tab test case per test can be selected one of the above created status. which also will be automatically be filled at tab "Overview" 

Where I have at column "P" the status per test

for example

row 10 has status "To do"

row 11 has status "Failed"

row 12 has status " In progress"

In column "L" (of tab overview) I want to use an formula that automatically shows what the status is over the group of tests. (one group is row 10, 11 and 12)

So if 1 state is "To do" in row 10 till 12  of column P than show the text "To do"

If  that state is not present but "In progress" Is then show in progress as text

if that is not present than show the text "Passed"

if that is not present than shown the text "Accepted by PO"

If that is not present than show the text "Failed"

If all is not present or field is empty than shown "Not tested" as text. 

I have no clue how what the best way is to approach this. 

Can you help me with this question??

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-07-22T14:19:04+00:00

    Hi Brenda

    Please try the formula

    =IF(COUNTIF($S$17:$S$19,"To do")>0,"To do",IF(COUNTIF($S$17:$S$19,"In progress")>0,"In progress","Not tested"))

    Note: Please, Adapt the ranges in the formula according to your scenario.

    Do let me know if you need more help

    On the other hand,

    If the answer helped you.

    Please, consider marking this thread as answered.

    It would help others in the community with similar questions or problems.

    Thank you in advance

    Regards

    Jeovany

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2020-07-22T13:22:20+00:00

    Hi Brenda,

    Please try the formula I created for this issue:

    =IF(OR(S17:S19=Q5),Q5,IF(OR(S17:S19=Q6),Q6,IF(OR(S17:S19=Q7),Q7,IF(OR(S17:S19=Q8),Q8,IF(OR(S17:S19=Q9),Q9,Q10)))))

    Note: Q5 = To do

    Q6 = In progress

    Q7 = Passed

    Q8 = Accepted by PO

    Q9 =Failed

    Q10 =Not tested

    You may transfer these to Configuration tab and please make sure to replace the cell references in the formula with correct ones.

    Please let me know if it worked out well for you. If not, please reply back to this forum.

    Regards,

    John

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-07-30T10:33:33+00:00

    Hi Jeovany and John, 

    Both of you thanks for the reaction.

    after a bit of puzzeling I found out that the formula should be written in Dutch because my excel has the dutch settings. ( I really don't like it but haven't found out how to change it to English.

    When I changed the formula into dutch both are working. At the end the formula provide by Jeovany fits better in what i try to achieve. 

    So this is what i made of it: 

    =ALS(AANTAL.ALS($S$17:$S$19,"")>0,"Unknown!",ALS(AANTAL.ALS($S$17:$S$19,"To do")>0,"To do",ALS(AANTAL.ALS($S$17:$S$19,"In progress")>0,"In progress",ALS(AANTAL.ALS($S$17:$S$19,"failed")>0,"Failed",ALS(AANTAL.ALS($S$17:$S$19,"Accepted by PO")>0,"Accepted by PO",ALS(AANTAL.ALS($S$17:$S$19,"Passed")>0,"Passed",ALS(AANTAL.ALS($S$17:$S$19,"Not tested")>0,"Not tested","Unknown!")))))))

    Because the status "Not tested" is an acceptable status and to see if an test is not executed or checked if the test should be executed, I have added when field is empty or has an status not listed then show "Unknown!" so action can be taken. 

    Again both of you many thanks for helping me with this issue

    Kind regards Brenda

    0 comments
  2. Anonymous
    2020-07-22T12:47:59+00:00

    Hi John, 

    Thanks for the response. 

    I have tried but I get errors when I try to exit the field.

    So an picture of what i'm at:

    In column S row 17, 18, 19 (blue highlighted) is the status of the scenario filled in.

    In field "P16" ( Pink highlighted) I want to show the overall status of the 3 scenario's combined. So when in the column S row 17,18 and 19 the status "To do" is present, show in field P16 the text "To do". If that status is not present than check than if the status "In progress" is present, if so fill in the status "In progress", if that is not present, check than in the following order the status Failed, Accepted by PO, Passed and at last Not tested. if those all are not present or status is "Not tested" than fill the test "Not tested". 

    I know it's a complex one but if you could help me along it would be really great!

    Greets Brenda

    0 comments
  3. Anonymous
    2020-07-22T11:08:48+00:00

    Hi,

    I am John, an Independent Advisor and Microsoft user like you. I'm here to work with you on this issue.

    As I understood correctly, what you will be needing to achieve this outcome is to use IF function (nested IF). Below is the link for sample on how to use this function.

    https://support.microsoft.com/en-us/office/if-f...

    I hope it helps you. If not, please let me know what else I can help you with.

    Regards,

    John

    0 comments