Excel 2016: Multiple conditions with the AND function

Updated 2017.07.30

Objectives:

·        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.

AND function overview

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.

AND function arguments

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

Example

From the workbook, use the Gradebook AND worksheet for the following example. The rule for passing the class is that the student must pass both the homework and the test. The only student who meets both criteria (see below) is George Washington.

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image001.jpg

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.

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image002.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image003.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image004.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image005.jpg

 

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:

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image006.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image007.jpg

 

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:

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image008.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image009.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image010.jpg

 

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

http://old.briarcliff.edu/departments/cis/Excel/2013/015-And-Function_files/image011.jpg

 

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.