CODEWITHSUNDEEP
excel-formula
CadWizard
CadWizard

Reputation: 13

IF, AND and OR statement

I have an Excel query around an IF, AND and OR statement. I have spreadsheet based around city center stores being open when they are not supposed to be.

I have shown a snippet of the spreadsheet and in plain English I need to do the following:

IF E2 is "Open" AND F2 is "YES" then status should read "Normal"

IF E2 is "Open" AND F2 is "NO" then status should read "Warning, this store should be open"

IF E2 is "Closed" AND F2 is "YES" then status should read "Warning, this store should not be open"

I have had a go at it and have come up with 2 AND statements (Shown below) which work independently. I have been reading up about nested statements and it looks like this is what I need to do here, however I just can`t get my head around how I do actually stitch all of it together in to 1 statement.

=IF(AND(E2="Open",H2="NO"),"Warning, this store should be open","Normal")

=IF(AND(E2="Closed",H2="YES"),"Warning, this store should not be open","Normal")

Upvotes: 1

Views: 55

Answers (1)

Martin
Martin

Reputation: 16423

You've mentioned using AND in your formula, but I think this is easily achieved with nested IFs:

=IF(E2="Open",IF(F2="YES","Normal","Warning, this store should be open"),IF(F2="YES","Warning, this store should not be open",""))

How to visualise what the above statement does:

E2               F2               Output
Open             YES              Normal
Open             (anything else)  Warning, this store should be open
(anything else)  YES              Warning, this store should not be open
(anything else)  (anything else)  (empty string)

It might be that you want other behaviour dependent upon other values of E2 and F2, but given your requirement the above will work.

Upvotes: 1

Related Questions