Test Multiple Conditions with Google Spreadsheets' AND and OR Functions

01
of 01

How to Use the AND and OR Functions in Google Spreadsheets

Test Multiple Conditions with Google Spreadsheets AND/OR Functions
Test Multiple Conditions with Google Spreadsheets AND/OR Functions. © Ted French

AND and OR Function Overview

The AND and OR functions are two of Google Spreadsheets' 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.

TRUE or FALSE ONLY

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 AND Function, formulas in multiple cells are tested and only if all of these formulas are true will the function returns a TRUE response. If not, the function returns FALSE as a value.

In the case of the OR function, if any one of the tested formulas is true, then the OR function returns an answer of TRUE. Only if all formulas are not true will OR give you a FALSE value in the cell where it is located.

Combining with Other Functions

These TRUE or FALSE answers for the AND and OR functions can be displayed as is in the cells where the functions are located or the functions can be combined with other Google Spreadsheet functions - such as the IF function to give display a variety of results or carry out a number of calculations.

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

AND FALSE OR TRUE

For the AND function in cell B2, 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 B3, 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.

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 AND function is:

= AND ( logical_expression1logical_expression2, ... )

The syntax for the OR function is:

= OR ( logical_expression1logical_expression2, logical_expression3, ... )

logical_expression1 - (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.

logical_expression2, logical_expression3, ...  - (optional) additional conditions that can be tested.

Entering the AND Function

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

Google Spreadsheets does not use  dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell B2 to make it the active cell - this is where the AND function is entered and where the function's result will be displayed
  2. Type the equal sign ( = ) followed by the name of the function AND
  3. As you type, the auto-suggest box appears with the names of functions that begin with the letter A
  4. When the name AND appears in the box, click on the name with the mouse pointer to enter the function name and an open round bracket into cell B2

Entering the Function's Arguments

The arguments for the AND function are entered after the open round bracket. As in Excel, a comma ( , ) is inserted between the function's arguments to act as a separator.

  1. Click on cell A2 in the worksheet to enter this cell reference as the logical_expression1 argument
  2. Type < 50 after the cell reference
  3. Type a comma ( , ) after the cell reference to act as a separator between the function's arguments
  4. Click on cell A3 in the worksheet to enter this cell reference as the logical_expression2 argument
  5. Type <> 75 after the cell reference
  6. Type a second comma ( , )  to act as another separator
  7. Click on cell A4 in the worksheet to enter the third cell reference
  8. Type >=100 after the third cell reference
  9. Press the Enter key on the keyboard to enter the closing round bracket " ) " after the arguments and to complete the function
  10. The value FALSE should appear in cell B2 because the data in cell A4 does not meet the condition of being greater than or equal to 100
  11. When you click on cell B2, the complete function =AND(A2<50,A3<>75,A4>=100) appears in the formula bar above the worksheet

OR instead of AND

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

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

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

  • the data in cell A2 is less than 50;
  • the data in cell A3 does not equal 75.
Format
mla apa chicago
Your Citation
French, Ted. "Test Multiple Conditions with Google Spreadsheets' AND and OR Functions." ThoughtCo, Aug. 23, 2016, thoughtco.com/test-multiple-conditions-3123358. French, Ted. (2016, August 23). Test Multiple Conditions with Google Spreadsheets' AND and OR Functions. Retrieved from https://www.thoughtco.com/test-multiple-conditions-3123358 French, Ted. "Test Multiple Conditions with Google Spreadsheets' AND and OR Functions." ThoughtCo. https://www.thoughtco.com/test-multiple-conditions-3123358 (accessed January 17, 2018).