There may be times in Excel when you require more than one condition to be true in order to take action or determine an outcome. This is where the AND function proves useful.
AND function in Excel
The AND function in Excel takes a variable number of parameters. Each parameter is a condition you are testing. For example, let’s say you want to test to make sure the values in cell A1 and A2 are both greater than 100, as in the sample spreadsheet below.
Our formula, using the AND function, would look like this:
=AND(A1>100,A2>100)
Result: TRUE
Now, if you wanted to also test to see if cell A3 > 100, in addition to cells A1 and A2, you would change your AND function to look like this:
=AND(A1>100,A2>100,A3>100)
Result: FALSE
Check out our Excel Lesson Videos to learn how to use more conditional logic functions in Excel.
Every condition must be TRUE
The result of the AND function above is FALSE because the value in cell A3 is 98, which is not greater than 100. The AND function will only return TRUE if every condition within the function’s parameter list returns a TRUE value. If even one condition returns a FALSE value, the entire AND function will return a FALSE value.
Use in conjunction with the IF function
The AND function is particularly useful when used in conjunction with the IF function. Suppose you need to determine who passes or fails a course based on the scores from two exams. In order to pass, a student must have a score higher than 70 on both exams. This is depicted in the sample spreadsheet below:
As shown in the sample above, the formula for determining Pass or Fail for the first student (row 2) is as follows:
=IF(AND(A2>70,B2>70),”Pass”,”Fail”)
Result: Pass
The result of this formula is Pass because both exam scores (95 and 82) are greater than 70. The remaining two students would fail because at least one of their exam scores is 70 or lower.
Remember, the AND function in Excel can take a variable number of parameters, each of which must evaluate to TRUE for the AND function to return a TRUE value. One or more FALSE conditions will cause the AND function to return FALSE.
Comments are closed.