# Functions AND – OR with Excel

Last Updated on 12/05/2024

Functions AND and OR are 2 logic functions that help you to create sophisticated logical tests with Excel.

1. The AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

2. The OR function

A condition OR returns TRUE if one (or more) tests of the function is true.

## Presentation of the functions

The AND and OR functions are very useful with logical tests in Excel. Basically, a logical test compares two items only. But if you want to create a test with more than one condition, you must use this function.

### AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

=AND(test 1,test 2,test 3, ...)

For instance, you may have a list of hotels based on criteria such as the number of stars (classification), the price of the menu, etc. You want to select a hotel according to these criteria

• At least 3 stars (B2>=3)
• The price of the menu is a maximum of 35 (C2<=35)
• And the hotel has a Fitness room (E2="Yes")

So the formula is

=AND(B2>=3,C2<=35,E2="Yes")

And when you copy the formula for all the hotels, you have this result

As you can see, the test returns TRUE only if all the 3 criteria are TRUE.

### OR function

A condition OR returns TRUE if one (or more) tests of the function is true. So, you use the OR function in a different situation where you accept many cases.

= OR(test 1,test 2,test 3, ...)

For instance, here, you want to select only the people who are single or divorced

=OR(F2="Single",F2="Divorced")

### Combination of AND and OR functions

But also, you can combine the 2 functions to create more sophisticated tests. For instance, we still want to select people who are divorced or single but also with no child. The formula is

=AND(G2=0,OR(F2="Single",F2="Divorced"))

Explanations:

• First, the test will check the status Single or Divorced. The OR will return TRUE when one of these values is found.
• Then, we embed the previous in the AND function to return TRUE when both results are TRUE

## Frédéric LE GUEN

24/11/2020 @ 10:41

I'm asking for consideration to check the answer key for question 3 because I believe that the correct function for it would be "AND". Please reply to my comment if I'm right or wrong to also correct myself. Thank you!

• Frédéric LE GUEN
24/11/2020 @ 11:13

Hi, yes, good catch. It's corrected. Thanks

# Functions AND – OR with Excel

Last Updated on 12/05/2024

Functions AND and OR are 2 logic functions that help you to create sophisticated logical tests with Excel.

1. The AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

2. The OR function

A condition OR returns TRUE if one (or more) tests of the function is true.

## Presentation of the functions

The AND and OR functions are very useful with logical tests in Excel. Basically, a logical test compares two items only. But if you want to create a test with more than one condition, you must use this function.

### AND function

A condition AND returns TRUE if and only if all tests in parenthesis are true.

=AND(test 1,test 2,test 3, ...)

For instance, you may have a list of hotels based on criteria such as the number of stars (classification), the price of the menu, etc. You want to select a hotel according to these criteria

• At least 3 stars (B2>=3)
• The price of the menu is a maximum of 35 (C2<=35)
• And the hotel has a Fitness room (E2="Yes")

So the formula is

=AND(B2>=3,C2<=35,E2="Yes")

And when you copy the formula for all the hotels, you have this result

As you can see, the test returns TRUE only if all the 3 criteria are TRUE.

### OR function

A condition OR returns TRUE if one (or more) tests of the function is true. So, you use the OR function in a different situation where you accept many cases.

= OR(test 1,test 2,test 3, ...)

For instance, here, you want to select only the people who are single or divorced

=OR(F2="Single",F2="Divorced")

### Combination of AND and OR functions

But also, you can combine the 2 functions to create more sophisticated tests. For instance, we still want to select people who are divorced or single but also with no child. The formula is

=AND(G2=0,OR(F2="Single",F2="Divorced"))

Explanations:

• First, the test will check the status Single or Divorced. The OR will return TRUE when one of these values is found.
• Then, we embed the previous in the AND function to return TRUE when both results are TRUE