Midlands Microsoft Office Provider

4.3 The AND Function – Intro to Excel 2016 Data Analysis

4.3 The AND Function

  • The AND function is great to use when you have 2 or more conditions that both need to be met in order to get a TRUE result (FIGURE 1).
  • Returns TRUE output if all its arguments evaluate to TRUE. Returns FALSE output if one or more arguments evaluate to FALSE.
  • A common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function (we will cover it also) performs a logical test and returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE.
  • By using the AND function as the logical test argument of the IF function, you can test many different conditions instead of just one. 

Picture

FIGURE 1
If any Function Argument input is FALSE, the output is FALSE.
The AND function can take up to 255 inputs.
​SCENARIO: You have 5 people who work for you and you want to be able to flag who is working overtime. You also are concerned with situations when they are working less than 35 hours also, so that would be a situation that should also be flagged. 
INSERT AND TABLE WITH DATA HERE. THIS IS FIGURE 2.
​Practice Exercise 5:  Concerning the AND function, use the above table to construct arguments for each employee. Use the Insert Function dialog box to construct each argument.
STEP 1A: Enter data from AND table above.

STEP 1B: Make cell C2 the active cell.

STEP 2A: Select Insert Function (SEE OPTIONS BELOW)
Ensure your data entered is in the same cells as in the example above.
​STEP 2B: There are multiple ways to select Insert Function:

​Option 1: Select Icon on the Formula Bar (FIGURE 3).
Option 1: Select the Insert Function icon from Formula Bar

Picture

FIGURE 3
Option 2:  From the Function Library section (FIGURE 4).
​Option 2: Select Insert Function icon from the Function Library section.

Picture

FIGURE 4
Whether you select Option 1 or Option 2, the “Insert Function” dialog box will appear, as shown in FIGURE 5.
STEP 3A: From the “Or select a category” drop-down arrow, select “Logical”.

STEP 3B: The Logical category functions will appear including the AND function. Ensure AND is highlighted. If you do not see it, scroll down. 
​
STEP 3C: Once visible, click it to highlight it.

​STEP 3D: Select the OK button.

Picture

FIGURE 5
View the data table to verify you are entering the correct arguments in the “Function Arguments” dialog box below (FIGURE 6).

​STEP 4A: ​Joe’s hours are in cell B2; that will be part of our 1st argument.
Our scenario is checking Joe’s hours to see if they are more than 34 hours AND less than 41.
The Greater Than and Less Than operators will do it:
B2>34 ARGUMENT #1
B2<41 ARGUMENT #2
​Select the OK button.


Picture

FIGURE 6
STEP 4B: Joe’s hours are in cell B2; that will be part of our 1st argument.
Our scenario is checking Joe’s hours to see if they are more than 34 hours AND less than 41.
The Greater Than and Less Than operators will do it:
B2>34 ARGUMENT #1
B2<41 ARGUMENT #2
Notice that as you are entering the arguments, they are also being entered into cell C2 as shown by the graphic on the right.

STEP 4C: Select OK button.
STEP 5A: In cell C2 the correct AND function result for Joe’s hours is TRUE; meaning he is good, hour-wise.

STEP 5B: Move mouse over to the bottom-right corner of cell C2 and Autofill down through cell C6 to obtain the AND results for the other employees.
STEP 6: ​Your results for the 5 employees hours should be as shown in the graphic on the right.
 

Since Joe, John, and James worked hours that were within the confines of this scenario, they had a TRUE result. Tom and Susan did NOT work hours within the confines, thus FALSE.
END OF PRACTICE EXERCISE 5