Preview
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.
With the AND Function, both argument inputs (or conditions) have to be TRUE to get a TRUE output (FIGURE 1). If any Function Argument input is FALSE, the output is FALSE.
The AND function can take up to 255 inputs.
Practice Exercise 5: Concerning the AND function, use the worksheet table shown in FIGURE 2 below to construct arguments for each employee. Use the Insert Function dialog box to construct each argument.
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.
The AND function uses arguments that include mathematical operators. These operators include:
Condition Symbol
Greater than >
Less than <
STEP 1A: Enter data from AND table above. Make cell C2 the active cell (FIGURE 2).
STEP 2: Select Insert Function. There are multiple ways to select Insert Function:
1. Insert Function Icon on the Formula Bar (FIGURE 3).
2. From the Formula tab on the Ribbon. Insert Function is at the left end of the Ribbon (FIGURE 4).
Whether you select Option 1 or Option 2, the “Insert Function” dialog box will appear, as shown in FIGURE 5.
STEP 3A: Once the Insert Function dialog box appears, in the “Select a function” section, scroll down until you find the AND function (FIGURE 5).
STEP 3B: If you do not see the AND function listed, enter “AND” in the “Search for a function” window and select the “Go” button on the right. That will display AND function in the function list.
STEP 3C: Select the OK button.
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.
Enter the following into the Function Arguments dialog box shown in FIGURE 6.
In the “Logical1” box, enter: B2>34
In the “Logical2” box, enter: B2<41
Select the OK button.
STEP 4B: Notice that as you are entering the arguments, they are also being entered into cell C2 as shown by FIGURE 7.
STEP 4C: Select OK button.
STEP 5: When we select the OK button on on Function Arguments dialog box, the AND formula will execute with the result. The result for Joe’s hours is TRUE; meaning he is good, hour-wise (FIGURE 8).
STEP 6: Move mouse over to the bottom-right corner of cell C2 until a small thin black cross becomes visible (FIGURE 9). Move your mouse downward through cell address C6 and release the mouse button (Autofill feature).
STEP 7: Your results for the 5 employees hours are shown in FIGURE 9.
Since Joe, John, and James worked hours that were within the confines of this scenario, they had a TRUE result. Tom (over 40 hours) and Susan (under 35 hours) did NOT work hours within the confines, thus they had a FALSE output.
There are 2 important advantages we enjoy when we use the Insert Function tool.
- When we input the arguments, Excel automatically enters these arguments into the worksheet cell address C2.
- Excel automatically enters the arguments in a correct AND formula format.
END OF LESSON
Leave a Reply