Microsoft Excel Formulas: Logical Formulas: AND

In mathematics and logic, a two-place (or multiple place) logical operator or logical conjunction results in TRUE if both (or all) of the operands are TRUE; otherwise it will result in FALSE. Writing this down logically taking operand one as A and operand two as B:

A ^ B returns TRUE if both A and B are TRUE, otherwise A ^ B returns FALSE.

To break this down in real terms, take an example of going to a concert. If Johnny and Peter can both afford to go to the concert then they will by tickets; if one of them cannot afford to go to the concert then they will not buy tickets.

Using an AND in this case and creating a truth table of A ^ B for you get the following (where A represents whether Johnny can afford the concert and B represents whether Peter can afford the concert):

So using the table:

(A) Johnny can afford the concert (TRUE)

(B) Peter can afford the concert (TRUE)

A ^ B = TRUE therefore they can go to buy the tickets.

Any other combination would result in a FALSE and therefore they would not buy tickets.

• OR: returns a TRUE value if any of the conditions are TRUE. Syntax: OR(logical1, logical2, logical3, ….)
• IF: performs a logical test on multiple logical arguments and provides a result based on a TRUE or a FALSE value. Syntax: IF(logical, True Result, False Result)
• IFERROR: returns a value if a logical function evaluates to an error, otherwise returns the result of the logical function. Syntax: IFERROR(logical, logical_error)
• FALSE: returns the value FALSE. Syntax: FALSE()
• TRUE: returns the value TRUE. Syntax: TRUE()
• NOT: reverses the logic of the argument. Syntax: NOT(logical)

In Microsoft Excel, AND is a logical function that returns a TRUE value if all its arguments are true, otherwise it will return a FALSE value. It performs in exactly the same way as AND in logic and mathematics.

Syntax: AND(logical1, logical2, logical3, …) where logical1, logical2, logical3 are conditions that will return a TRUE or FALSE result.

Notes:

• The AND function in Excel must have one condition and can have a maximum of 255 conditions (30 in Excel 2003 or prior).
• Each condition must produce a logical value TRUE or FALSE (blank arguments or arguments containing text are ignored).
• If there are no logical values the AND function will return the #VALUE error value.

There are many uses for the AND function within Microsoft Excel:

• It can be used to test whether a number is within a certain range: =AND(number>=1, number<=6) will test whether the number is between 1 and 6 and return a TRUE value if the number meets the conditions.
• It can test criteria such as Employee and Office: =AND(Employee=”Cook”, Office=”Grimsby”) will test to see if the Employee is “Cook” and his Office is “Grimsby” and will return a TRUE value if these conditions are both TRUE.
• It can test criteria such as Employee and Sales: =AND(Employee=”Smith”, Sales>599) will test that the Employee is Smith and that her Sales are greater than 599 and will return a TRUE value if these conditions are both TRUE.
• When used in conjunction with other functions such as IF and OR it becomes very powerful: =IF(AND(OR(Employee="Cook", Employee="Smith"),Sales>599),"Result 1","Result 2") will first take the logical OR function and evaluate it – so if the employee is either Cook or Smith this part of the function will return TRUE; it then will evaluate the second part of the function Sales>599 and if Sales are greater than 599 then this will return TRUE. The AND function evaluates these two conditions and if both are TRUE then it returns TRUE. The IF statement will return ‘Result 1’ if the AND function is TRUE and ‘Result 2’ if the AND function is FALSE. So if the employee is Cook or Smith AND their Sales are greater than 599 then the function returns ‘Result 1’, any other combination will return ‘Result 2’.

The logical functions on Microsoft Excel allow for logical analysis on sets of data. The AND function tests conditions logically and allows for data to be filtered based on criteria. When used in conjunction with the other logical functions (OR, FALSE, TRUE, IFERROR and NOT) it becomes an essential tool for worksheet designers.