# Microsoft Excel Formulas: Logical Formulas: AND

## Definition of AND (logical conjunction)

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.

## Quick overview of other logical functions in Microsoft Excel:

**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)

## Microsoft Excel AND Function

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.

## When to use an Excel AND function:

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

## Conclusion

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.

## More by this Author

- 3
This article describes how to create a diagram in Excel using Smart Art. A diagram that looks professional can be a very useful tool in promoting a point within an article, or to explain a concept without having to...

- EDITOR'S CHOICE2
Microsoft Excel is a very powerful tool that allows you to produce professional line graphs very quickly and easily. With intuitive functionality it's possible to create professional and relevant graphs with the click...

- EDITOR'S CHOICE20
Microsoft Excel has become the standard spreadsheet for most business use, but with it's functionality and ease of use the advantages of Excel can be used at home as well.

## Comments

No comments yet.