Excel Tip of the Week #127 - AND / OR revisited

Hello and welcome back to the Excel Tip of the Week.  This week, we have a General User post in which we're revisiting the AND and OR logical functions, originally introduced back in TOTW #15.

I use these two functions regularly, and they are really effective, particularly when combined with the IF function – some more on that below.
 
What do these functions do?

These functions work with logical comparison formulas, like A1>5, that return a value of either TRUE or FALSE as their result.  AND and OR are based in the principles of Boolean logic, and let you combine the results of two or more such comparisons: 
     - The AND function returns TRUE when ALL of the logical comparisons are TRUE.
     - The OR function returns TRUE when ANY of the logical comparisons are TRUE.

A summary of the AND/OR results based on two logical comparisons is shown below.



How to write these functions

The syntax for a AND/OR function looks like this:
     =AND(Argument 1, Argument 2, Argument 3,...)
     =OR(Argument 1, Argument 2, Argument 3,..)
 
     - These functions do not have a set number of arguments; they can have as few or as many as you need – the same rules as above still apply.
     - The functions can also be written with a reference to a range of cells which contain the TRUE/FALSE values, e.g. =AND(A1:A2)
     - Alternatively, if you want TRUE to return a number and FALSE to return a zero you can simply multiply a number by the AND function i.e.: = [number] * AND(A1>B1,A2>B2).  This is because if you use a TRUE/FALSE value in a multiplication, Excel treats TRUE as 1 and FALSE as 0.
 
What to use these functions for
 - Where you need to do something based on the overall outcome of multiple logical comparisons.
 - Creating more complex conditions for Conditional Formatting (see TOTW #73)
 - Determining what should be in a cell depending on a date range (take a look at the example at the foot of this post)
 - Applying timing/date/conditional multipliers or functions (i.e. interest, inflation, calculating a number of time periods)
 - Combining the results of multiple error checks into one master check
 
Nesting

The AND and OR functions can be combined with other Excel functions, to get to a formula that does exactly what you want it to:
 - The functions are often used within an IF statement:
     = IF( AND(A1>B1,A2>B2) , [do if true] , [do if false] )
        This formula will return [do if true] only if A1 is bigger than B1 AND A2 is bigger than B2
 - You can even combine with AND/OR into one statement:
     =IF(AND(OR(A2="", A2="(blank)"), OR(B2="", B2="(blank)")), "BLANK", "NOT BLANK")
       This formula will return BLANK if both A2 and B2 are either empty, or contain the word "blank", or any combination of these

Previous post 
TOTW index
Next post

Thanks to Emma Horton for her input on this Tip.

Interested in coming to learn about spreadsheet risks and review techniques from David directly?  You can sign up for his upcoming ICAEW Academy course here.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

AND Example.xlsx
Anonymous