Functions AND – OR with Excel

Functions AND – OR with Excel
Last Updated on 19/01/2024
Reading time: 2 minutes

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

Presentation of the functions

The AND and OR functions are very useful with logical tests in Excel.

Basically, with a logical test, you compare two items only. But if you want to create a test with more than 1 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 have a list of hotels with different criteria like the number of stars (classification), the price of the menu, ...

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 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 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 will be found
  • Then, we embed the previous in the AND function in order to return TRUE when both result are TRUE

Exercises online with AND and OR in Excel

To learn how to use these functions, please go to that link to do an online exercise

You will learn how to select your client according to criteria like Gender, Income, Age, .... And also how to combine many criteria to create a rich selection.

Question to selection clients

If you want to do more online exercises, go to that page.

XOR function

You can create an exclusive OR (only one test is true) with the XOR function. This function is generally useful for electronic projects.

Have a look at this article if you want to learn more about it.

Convert TRUE or FALSE to 0 or 1

Do you know that you can convert the result of a test (TRUE or FALSE) to a numeric value (1 or 0) 😉👍

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 19/01/2024

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

Presentation of the functions

The AND and OR functions are very useful with logical tests in Excel.

Basically, with a logical test, you compare two items only. But if you want to create a test with more than 1 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 have a list of hotels with different criteria like the number of stars (classification), the price of the menu, ...

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 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 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 will be found
  • Then, we embed the previous in the AND function in order to return TRUE when both result are TRUE

Exercises online with AND and OR in Excel

To learn how to use these functions, please go to that link to do an online exercise

You will learn how to select your client according to criteria like Gender, Income, Age, .... And also how to combine many criteria to create a rich selection.

Question to selection clients

If you want to do more online exercises, go to that page.

XOR function

You can create an exclusive OR (only one test is true) with the XOR function. This function is generally useful for electronic projects.

Have a look at this article if you want to learn more about it.

Convert TRUE or FALSE to 0 or 1

Do you know that you can convert the result of a test (TRUE or FALSE) to a numeric value (1 or 0) 😉👍

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 *