Logical functions in Google Sheets – IF, AND, OR.
Syntaxes=IF(condition, value_if_true, value_if_false)
=AND(logical_expression1, [logical_expression2, ...])
=OR(logical_expression1, [logical_expression2, ...])
Sample Usages=IF(B2>40, “Pass”, “Fail”)
//Returns “Pass” if the value in B2 is greater than 40; else “Fail”.=AND(A1<100, A2<100)
//Returns TRUE if both A1 and A2 are greater than 100, else returns FALSE.=OR(A1<100, A2<100)
//Returns TRUE if either of the value is greater than 100, otherwise FALSE.
We will have a look at individual logical functions together in Google Sheets ( AND, OR, IF ) and see how to use them together along with some examples.
Why use logical functions together in Google Sheets
Logical functions are used in spreadsheets to test whether a situation is true or false.
Google Sheets gives you access to very powerful logical Functions such as IF, AND and OR functions. These should cover most of the cases but there might be more advanced case scenarios where using these alone won’t suffice. In that case, we will have to be little creative and use the logical functions together in Google Sheets.
We will cover these cases in detail later and see how to come up with logic to use logical functions together in Google Sheets.
IF logical operator
If the logical operator is a conditional operator that returns one value if a certain condition is met, otherwise it returns another value. The syntax for the same is
=IF(condition, value_if_true, value_if_false)Sample Usage
=IF(B2>40, “Pass”, “Fail”)
Here in this example, we are checking if the student has scored more than 40 marks. If yes, we declare him passed otherwise failed.
AND logical operator
AND operator is an operator which returns true only if all the arguments are true. Otherwise, it returns false. The syntax for the same is as follows:
=AND(logical_expression1, [logical_expression2, ...])There can be multiple arguments for this function.
Sample usage
=AND(A1<100, A2<100)
Here B4 was true only because both the values A1 and A2 are less than 100.
OR logical function
OR is a logical operator that returns TRUE if any one of its arguments is true, else, it returns false. The syntax for the same is as follows:
=OR(logical_expression1, [logical_expression2, ...])Just like AND logical function, there can be multiple arguments for this function.
Here were can see since A1 is less than 100, the result is TRUE.
Combined use of IF, AND, OR logical functions
Let’s have a look at two situations.
The first situation is a report card. If the student has obtained a mark higher than 40 in all the subjects, he is declared Passed, otherwise Failed.
To achieve the given task we have used the IF function along with AND.
Formula used:
=IF(AND(B2>40, C2>40, D2>40), "Pass", "Fail")The AND function returns true or false and based on that the IF function displays the text.
In the next example, let’s have a look at a sports match. If the player wins any two rounds, then he or she is declared the winner. This problem can be solved by using the given formula.
Formula used:
=IF(OR(AND(B2=1, C2=1), AND(C2=1, D2=1), AND(B2=1, D2=1)), "Win", "Lose")We are using the combination of IF, OR & AND. There can be three combinations possible to win for the AND condition. If any of those is true, then the player would win the match. Hence, the OR condition. Lastly, we have used the IF condition to conditionally display the text.
Conclusion
Now you know how to use OR, AND, IF logical functions together in Google Sheets along with the syntax for each and every individual function. We also saw some interesting examples where they can be used.
See Also
Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Validate URLs in Google Sheets: Learn how to Validate URLs using Conditional Formatting in Google Sheets
Google Sheets Left and Right Functions: Learn how to use the LEFT and RIGHT Functions in Google Sheets.