Getting Jiggier with IF. Utilizing AND / OR to increase the functionality of IF

 Are you ready to bring your IF functions to the next level?!? 

=IF(user response = "YES", Continue Reading, Revisit the post on IF functions)

Hopefully if you're reading this post you can follow along with my terrible IF joke. If you have no idea what I'm talking about or just need a refresher on how to use the IF function take a look this post here. 

By itself, the IF function is quite powerful.With it we can seamlessly apply logic to our excel spreadsheets and create dynamic workbooks that are sure to impress your coworkers, boss, their boss, etc. But what if you needed to evaluate multiple conditions? What if these all needed to be TRUE? What if just one of these needed to be TRUE?  This is where the AND / OR functions come into play

What do these functions do

AND / OR are both logic functions that are used to connect two or more expressions. The evaluated expressions can then be passed on to a larger more comprehensive formula

• AND - Checks whether or not all arguments result in TRUE
  
• OR    - Checks whether or not any of the arguments result in TRUE

 

Why are these functions important

AND / OR extends the logic used in an Excel formula allowing us to evaluate multiple expressions rather than just a single expression. This greatly extends the capabilities of the IF function. 

 

How do we use these functions

Lets start with the arguments

• =AND(logical 1, logical 2,...) 
• =OR(logical 1, logical 2,...) 

           AND / OR support a total of 255 arguments. Logical[1-255] are any conditions that can result in either TRUE or FALSE.

 

Quick examples

=AND(1=1 , A=B)

Result: False

=OR(1=1 , A=B)

Result: True 

 

 

How are these used with an IF function 

 Now that we got the basics out of the way, lets go into examples of how to use AND / OR with IF. 

Suppose you were given a list of users. This data contains name, age, gender, and favorite animal 

 

You are given the following assignment. Identify all users between the ages of 30 and 39. Mark these users as belonging to "Campaign 1", all other users should be assigned to "Campaign 2"

The assigned task can be done in excel through a two part formula containing both an IF and AND function. Lets take a look at what that formula would look like, first starting with the AND portion.

AND
= AND([AGE] >=30,[AGE] <=39)
This function looks at a users age and evaluates if its both greater than or equal to 30 and less than or equal to 39. Any user between the age of 30 and 39 will result in a TRUE. We can now grab that formula and enter it into our IF function to get the final result

=IF(AND([AGE] >=30,[AGE] <=39), "Campaign 1" , "Campaign 2")

 After applying the formula to our spreadsheet this end result

After the success of your assignment, management has requested your help on a new task. This time around they would like you to identify all users who's favorite animal is either a cat or a dog and to  include that user in the CAT DOG Newsletter. Users who are not included in that list should not be part of any newsletter 

Again, the assigned task can be done in a two part formula but this time with an IF and an OR. Lets take a look at what this formula would like like, starting with the OR

=OR([Favorite Animal] = "Cat",[Favorite Animal] = "Dog")

This function look at the users favorite animal and evaluates if that animal is either a cat or a dog. Any user who's favorite animal is a cat or a dog will result in a TRUE.  We can now enter that into our IF function 

=IF(OR([Favorite Animal] = "Cat",[Favorite Animal] = "Dog"),"CAT DOG Newsletter","")

 After applying the formula to our workbook here is our result