CODEWITHSUNDEEP
excelexcel-formula
louis
louis

Reputation: 625

IF Else with AND, OR in Excel

I have the excel data in the below format:

severity    Type    Overview    Classification  Result
critical    defect  closed      Fix             Good
serious     enhance no change   no fix          Low
Medium      defect  no change   no fix          Low
Low         enhance closed      fix             Poor
critical    defect  Duplicate   fix             Good
Medium      defect  Duplicate   fix             Low
Low         defect  Duplicate                   Poor

I have written the formula to calculate the result, But i couldn't execute due my bad knowledge in excel. Any help would be appreciated.

Here are the conditions:

 Severity           Type               Overview     Classifcation       Result
Citical/Serious     Defect/Enhance      Closed          Fix                 Good
                    Defect/Enhance      Closed          No fix              Poor
                    Defect/Enhance      Closed          Updated             Low
                    Defect/Enhance      Duplicate       No fix              poor


Medium/low          Defect/Enhance      closed          No fix              Low
                    Defect/Enhance      Duplicate       No fix              Poor

Upvotes: 0

Views: 309

Answers (2)

Nicolai Ehemann
Nicolai Ehemann

Reputation: 574

It is not nice, but the solution to the conditions defined in your last table is like this:

=IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Fix");"Good";IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="No fix");"Poor";IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Updated");"Low";IF(AND(OR(A2="Critical";A2="Serious");C2="Duplicate";D2="No fix");"Poor";IF(AND(OR(A2="Medium";A2="Low");C2="Closed";D2="No fix");"Low";IF(AND(OR(A2="Medium";A2="Low");C2="Duplicate";D2="No fix");"Poor";"Undefined"))))))

Written in a more readable, multiline fashion (I'm not sure this will survive copy&paste):

=IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Fix");"Good";
   IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="No fix");"Poor";
     IF(AND(OR(A2="Critical";A2="Serious");C2="Closed";D2="Updated");"Low";
       IF(AND(OR(A2="Critical";A2="Serious");C2="Duplicate";D2="No fix");"Poor";
         IF(AND(OR(A2="Medium";A2="Low");C2="Closed";D2="No fix");"Low";
           IF(AND(OR(A2="Medium";A2="Low");C2="Duplicate";D2="No fix");"Poor";
             "Undefined"
           )
         )
       )
     )
   )
 )

As you can see, any combination you have not covered will result in "Undefined". You could probably combine some of the conditions, but that will not necessarily be more readable.

The niced solution would perhaps be a complete lookup table in a separate sheet with CHOOSE() and VLOOKUP()...

Upvotes: 1

Oligg
Oligg

Reputation: 375

Say you have your data in Range A1:D8, where your first line is the header. You can use this (ugly) formula which does exactly what you described in your conditions table:

=IF(AND(OR(A2="Critical",A2="Serious"),OR(B2="Defect",B2="Enhance")),IF(C2="closed",IF(D2="Fix","Good",IF(D2="No fix","Poor",IF(D2="Updated","Low"))),IF(AND(C2="Duplicate",D2="No"),"poor","No match")),
IF(AND(OR(A2="Medium",A2="Low"),OR(B2="Defect",B2="Enhance")),IF(AND(C2="closed",D2="No fix"),"Low","No match"),IF(AND(C2="duplicate",D2="No fix"),"Poor")))

The trick is to use the else condition as an elseif.. and when you have alot is makes it really hard to read.

Upvotes: 1

Related Questions