Based on the Boolean logic in Excel, all mathematical expressions will be simplified to 0 (FALSE) or 1 (TRUE) values.
What is Boolean logic?
Boolean logic is a form of algebra in which all values are converted to TRUE or FALSE. In Excel contexts, TRUE is equivalent to 1, and FALSE is equivalent to 0. This logic is named after George Boole, a mathematician who first defined an algebraic logic system in the mid-19th century.
Basic Boolean Operations
- AND (*): This operation returns TRUE (1) only if all conditions are TRUE. For example, (A > 10) * (B = “Yes”) evaluates to TRUE only if both conditions are met.
- OR (+): This operation returns TRUE if any conditions are TRUE. For example, (A > 10) + (B = “Yes”) will return TRUE if either A is greater than 10 or B is “Yes”.
- NOT: This inverts the Boolean value. If a condition is TRUE, NOT will make it FALSE, and vice versa. In Excel, this is written as NOT(condition).
Boolean Logic: Truth Tables
Here is an example of a truth table for the AND and OR operations:
| Condition 1 | Condition 2 | AND (*) | OR (+) |
|---|---|---|---|
| FALSE | FALSE | FALSE (0) | FALSE (0) |
| FALSE | TRUE | FALSE (0) | TRUE (1) |
| TRUE | FALSE | FALSE (0) | TRUE (1) |
| TRUE | TRUE | TRUE (1) | TRUE (1) |
Converting Complex Formulas to Boolean Expressions
When facing more complex conditions, breaking the formula into smaller Boolean expressions is sometimes easier. Consider the following example, where we want to determine if a product qualifies for a discount:
=IF((C3>100) * (D3="Yes") * (E3<50), "Discount", "No Discount")This formula checks three conditions:
- C3 must be greater than 100,
- D3 must indicate that the item is in stock (“Yes”),
- and E3 must be less than 50.
If all conditions are true, the result will be “Discount”; otherwise, it will return “No Discount”. Using Boolean logic in Excel simplifies the logic behind these operations, making it easier to understand and debug.
Related formulas: