Functions AND – OR with Excel

Last Updated on 12/05/2024
Reading time: 2 minutes

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

Selection of and hotel with the AND function of Excel

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

Select people single or 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"))

Combination of the functions AND and OR in Excel

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

2 Comments

  1. Bryan Daniel E. Madrio
    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!

    Reply

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

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Functions AND – OR with Excel

Reading time: 2 minutes
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

Selection of and hotel with the AND function of Excel

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

Select people single or 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"))

Combination of the functions AND and OR in Excel

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

2 Comments

  1. Bryan Daniel E. Madrio
    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!

    Reply

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

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *