Excel 2016: Multiple conditions with the AND function

Updated 2017.07.30

·
Learn
how to use the ** AND function **to make a decision about the
value to place in a cell.

Use this workbook for the examples on this page.

The **AND** function allows you to
look at up to 255 **conditions** (logical tests which can be either TRUE or
FALSE) and combine them into a single TRUE or FALSE value. The **AND*** *function
will return the value *TRUE* if *all *of its arguments are true,
otherwise it will return the value *FALSE. *It allows you to make
decisions (using the *IF* function) __based on more than one condition__.
For example, a teacher might want to create a spreadsheet that puts the letter
"P" in a cell if a student passes a class, and puts the letter
"F" in a cell if a student fails the class. Assume that there are two
conditions for passing the class: (1) the student must have an average of __70
or more on homework__, **AND** (2) the student must have a grade of __65
or more on the final test__. The student will only pass if *both* of
these conditions are true.

An **AND** function must always be
supplied with up to 255 arguments, all of which must be either *TRUE* or *FALSE*.

From the workbook, use the * Gradebook
AND *worksheet for the following example. The rule for passing the class
is that the student must pass

In Excel, we must use the *AND*
function to look at *both* conditions. We can make our work easier if we
divide the problem into smaller steps. We will use columns I and J for this. A
student passes the homework if his homework score is greater than or equal to
the minimum. For George Washington, this is: B2>=$F$2.

Use the fill handle to copy the formula to the next three rows:

Now put a logical test in J2 to determine if the student passed the Final Test:

Use the fill handle to copy the formula to the next three rows:

Now let's work on the *Pass Class? *column
(column K). The student passes the class if the value in column I is true AND
the value in column J is also true. So we need the AND function in column K:

Use the fill handle to copy the formula to cells K3:K5:

This tells us who passed (TRUE) and who failed (FALSE), but we want the letter "P" and the letter "F" to appear, not TRUE and FALSE. Now all that needs to be done is to put a formula in column E that will look at the value in column D to see if the student passed the class:

Use the fill handle to copy this formula to cells E3:E5. You should see the following values:

Note how simple all of the formulas are when we break the problem into small parts:

Note also that we could have combined all of the functions into a single formula:

Note that even though we have only
used two conditions in the arguments to the *AND* function here, there can
be as many as 255. __All__ of the conditions must be *TRUE* for the *AND*
function to return the value *TRUE*; otherwise, it will return the value *FALSE*.