Excel AND and OR Functions

Test Multiple Conditions with Excel's AND and OR Functions

The AND and OR functions are two of Excel's better known logical functions, and what these two functions do is to test to see whether the output from two or more target cells meets conditions that you specify.

01
of 09

TRUE or FALSE ONLY

Testing Multiple Conditions with Excel's AND and OR Functions
Testing Multiple Conditions with Excel's AND and OR Functions. &copy: Ted French

One feature of these functions is that they will only return or display one of two results or Boolean values in the cell where they are located - TRUE or FALSE.

  • For the OR function - row 2 above - multiple conditions are tested and if any one of the tested conditions is true, then the OR function returns an answer of TRUE. Only if all conditions are not true will OR give you a FALSE value;
  • For the AND Function - row 3 above - multiple conditions are tested and only if all of the conditions are true will the function returns a TRUE response. If not, the function returns FALSE as a value.
02
of 09

Combining with Other Functions

These TRUE or FALSE answers can be displayed as is in the cells where the functions are located or the functions can be combined with other Excel functions - such as the IF function - rows four and five above - to give a variety of results or carry out a number of calculations.

03
of 09

How the Functions Work

In the image above, cells B2 and B3 contain an AND and OR function respectively. Both use a number of comparison operators to test a variety of conditions for the data in cells A2, A3, and A4 of the worksheet.

The two functions are:

=AND(A2<50,A3<>75,A4>=100)
=OR(A2<50,A3<>75,A4>=100)

And the conditions they test are:

  • if the data in cell A2 is less than 50 (< is the symbol for less than)
  • if the data in cell A3 is not equal to 75 (<> is the symbol for not equal to),
  • if the data in cell A4 is greater than or equal to 100 (>= is the symbol for greater than or equal to).
04
of 09

AND FALSE OR TRUE

For the AND function in cell B3, the data in cells (A2 to A4) must match all three of the conditions above for the function to return a TRUE response.

As it stands, the first two conditions are met, but since the value in cell A4 is not greater than or equal to 100, the output for the AND function is FALSE.

In the case of the OR function in cell B2, only one of the conditions above needs to be met by the data in cells A2, A3, or A4 for the function to return a TRUE response.

In this example, the data in cells A2 and A3 both meet the required condition so the output for the OR function is TRUE.

05
of 09

AND/OR Functions' Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the OR function is:

= OR( Logical1 , Logical2 , ... Logical255 )

The syntax for the AND function is:

= AND ( Logical1 , Logical2 , ... Logical255 )

Logical1 - (required) refers to the condition being tested. The form of the condition is normally the cell reference of the data being checked followed by the condition itself - such as A2 < 50.

Logical2, Logical3, ... Logical255 - (optional) additional conditions that can be tested up to a maximum of 255.

06
of 09

Entering the OR Function

The steps below cover how to enter the OR function located in cell B2 in the image above. The same steps can be used for entering the AND function located in cell B3.

Although it is possible to type the entire formula such as

=OR(A2<50,A3<>75,A4>=100)

manually into a worksheet cell, another option is to use the function's dialog box - as outlined in the steps below - to enter the function and its arguments into a cell such as B2.

Advantages of using the dialog box are that Excel takes care of separating each argument with a comma and it encloses all arguments in parenthesis.

07
of 09

Opening the OR function Dialog Box

  1. Click on cell B2 to make it the active cell - this is where the AND function will be located
  2. Click on the Formulas tab of the ribbon
  3. Click on the Logical icon to open the function drop down list
  4. Click on OR in the list to open the function's dialog box

The data that will be entered into the blank rows in the dialog box will form the function's arguments.

08
of 09

Entering the OR Function's Arguments

  1. Click on Logical1 line of the dialog box
  2. Click on cell A2 in the worksheet to enter this cell reference
  3. Type < 50 after the cell reference
  4. Click on Logical2 line of the dialog box
  5. Click on cell A3 in the worksheet to enter the second cell reference
  6. Type <> 75 after the cell reference
  7. Click on Logical3 line of the dialog box
  8. Click on cell A4 in the spreadsheet to enter the third cell reference
  9. Type >=100 after the cell reference
  10. Click OK to complete the function and return to the worksheet
  11. The value TRUE should appear in cell B2 because the data in cell A3 does meet the condition of not being equal to 75;
  12. When you click on cell B2, the complete function =OR(A2<50,A3<>75,A4>=100) appears in the formula bar above the worksheet
09
of 09

AND instead of OR

As mentioned, the steps above can also be used for entering the AND function located in cell B3 in the worksheet image above.

The completed AND function would be: =AND(A2<50,A3<>75,A4>=100).

A value of FALSE should be present in cell B3 since it only one of the conditions being tested needs to be false for the AND function to return a FALSE value and in this example two of the conditions are false:

  • the data in cell A2 is not less than 50;
  • the data in cell A4 is not greater than or equal to 100.
Format
mla apa chicago
Your Citation
French, Ted. "Excel AND and OR Functions." ThoughtCo, Nov. 5, 2017, thoughtco.com/test-multiple-conditions-p2-3123749. French, Ted. (2017, November 5). Excel AND and OR Functions. Retrieved from https://www.thoughtco.com/test-multiple-conditions-p2-3123749 French, Ted. "Excel AND and OR Functions." ThoughtCo. https://www.thoughtco.com/test-multiple-conditions-p2-3123749 (accessed January 24, 2018).