When using Google Sheets, you often need to check your spreadsheet data to see if it meets specific criteria. AND/OR functions are a simple way to help you achieve this. These functions give you a TRUE or FALSE value and can be used to help sort your data.
This article explains how these functions work, the syntax, some examples, and how you can create a nested formula with the IF function.
The syntax of the AND function
Here is the AND function syntax in Google Sheets:
=AND(exp1, exp2)
In this formula, the first argument is required and the second is optional.
- exp1 – This is the first expression to enter into the formula. It can contain a cell address or a direct argument. The value must be something that can represent a logical value like TRUE or FALSE.
- exp 2 – This is an optional value. When added, both values must be TRUE or FALSE for the AND function to return a result. You can add other expressions if you wish.
How to use the AND function
Here are the steps to use the AND function in Sheets:
Click the cell where you want to type the function.
- Type =AND( then write your first argument.
- Add a comma, then write your second argument if necessary.
- Close the function with a closing parenthesis and press Walk in.
Examples of the AND function
To help you better understand the AND function, here are some examples of this formula in action.
Example 1: Finding TRUE values with the AND function
In this scenario, we are comparing the values of two different cells to see if they match the ones provided in the formula. In this case, both arguments are TRUE, which means that the value of AND is also TRUE.
Example 2: Searching for false values with the AND function
In this case, we deliberately created a situation where two inputs cannot be equal. We get a FALSE output when adding this argument in the AND formula.
The syntax of the OR function
Here is the syntax of the OR function in Google Sheets:
=OR(exp1, exp2)
Similar to the AND function, the first argument is required and the second is optional.
- exp1 – This is the first expression to enter in the formula and it can contain a cell address or a direct argument. The value must be something that can represent a logical value like TRUE or FALSE.
- exp 2 – This is an optional value. When added, both values must be TRUE or FALSE for the OR function to be true. Again, you can add more expressions if you need.
How to use the OR function
Here are the steps to use the OR function in Sheets:
- Click the cell where you want to run the function.
- Type =OR( then write your first argument.
- Add a comma and write your second argument.
- Close the function with a closing parenthesis and press Walk in.
Examples of the OR function
In the demo, both arguments give the output TRUE. Now let’s look at some more examples to better understand output in different circumstances.
Example 1: Finding TRUE values with the OR function
In this scenario, the first entry will obviously give the wrong answer. However, the second argument will give a TRUE output which satisfies the OR function because it only needs one value to be true.
Example 2: Finding FALSE values with the OR function
In this case, we enter the argument because the two values are not equal. However, both cells contain the same values, which means the returned output is FALSE.
The difference between AND and OR functions in Google Sheets
These two functions are a great way to ensure the validity of your data in Google Sheets. There is almost no difference between AND and OR functions, except:
- The AND function requires all arguments in the formula to return the value as TRUE to give a TRUE result.
- The OR function requires any of the arguments to return a true value of TRUE to return a TRUE result.
Nest AND with OR in Google Sheets
Let’s look at a practical example of AND/OR nesting. Let’s imagine that we are a teacher of a security course. Students must have passed 2 tests or taken an extracurricular course to earn a passing grade for the semester. The passing grade on the test is 5/10 and the extracurricular course is an ungraded pass at the end. Here’s how we could show this in a spreadsheet.
In the example above, we used the AND function as the first argument of the OR function. Students had to pass both tests. So the score in B2 and C2 must both be equal to or greater than 5 indicated by the >= operator.
The second argument of the OR function is D2=”Yes” which shows that the students have completed the additional training. If they meet any of the conditions, the formula will display a TRUE value. If none of the conditions, a FALSE value will be returned.
Nested functions with IF & AND/OR
To properly understand IF functions nested with AND and OR functions, we must first fully understand the IF function itself. Here is the syntax for that:
=IF(exp, val_true, val_false)
Here are the necessary arguments for the IF function:
- exp- This is a reference to a cell or value checked to see if TRUE or FALSE.
- val_true – This is the value to display if the exp the argument returns TRUE.
- val_false – This is an optional argument displayed if the exp argument is FALSE.
Now that we know how the IF and AND/OR functions work in Google Sheets, let’s see how we can use AND/OR together with the IF function to create a nested function.
The IF function will display one of two specified values based on an argument input. To understand, let’s look at an example and some steps on how to do it.
- Click on the cell and start typing =IF(
- Now write =AND( or =OR( depending on your needs.
- Write your arguments in the AND/OR formula and add a closing parenthesis.
- Enter the values to return for the TRUE and FALSE values, here we used ‘Yes’ and ‘No’.
- Finally, add another closing bracket to close the IF function and press Walk in.
When using the OR function with IF, only one of the arguments must be true to satisfy the equation. Using the AND function with IF requires all arguments of the AND formula to be satisfied to return TRUE in the equation. You can also nest Excel IF functions in the same way when importing or exporting documents.
Continue to Master Google Sheets
The AND and OR functions are among the simplest functions in Google Sheets. But you can use them to perform important calculations within larger nested formulas. There is still a lot to learn about Google Sheets. Keep practicing and you’ll be a pro in no time.
Read more
About the Author