So, to start off, what’s a Boolean? A Boolean is a data type with only two possible values, TRUE or FALSE. You’ll often see Boolean results, or Boolean expressions in Excel. For example, if I enter the formula =B5>30 here, we’ll get the Boolean result of TRUE. This is a Boolean expression—or logical statement—that returns either TRUE or FALSE. If I copy the formula down, we’ll get a Boolean result for every number in the list. What’s interesting about Boolean values is that they have numeric equivalents that can be used in formulas During a math operation, Excel will coerce Booleans into numbers, TRUE becomes “1”, and FALSE becomes “0”. I can demonstrate this with a formula that simply adds “0” to the values in column C. Zero plus any number is the same number, so you can see that TRUE is equivalent to “1” and FALSE is equivalent to “0”. So, how can you use Boolean logic in Excel? Let’s look at a simple example. Here we have a list of salespeople and sales numbers. Let’s say they get a bonus of $500 if they exceed $10,000 in sales. One way to handle that with a formula is to write: =IF(sales > 10000, 500, 0) And this works fine. But how could we use Boolean logic instead? Well, sales greater than $10,000 is a logical expression that returns either TRUE or FALSE. That means we can just rewrite the formula like this: =(sales > 10000) * 500 So, that’s nice. We got rid of an IF statement. But perhaps it seems silly just to get rid of a single IF statement. So let’s add a little complexity to the formula. Let’s assume there is a special bonus of $250 for sales over $12,000? In that case, you might see or write a nested IF formula like this: =IF(C5>12000,500+250,IF(C5>10000,500,0)) What about the Boolean version of this formula? Well, with Boolean logic, we can simply write: =(sales>10000)*500+(sales>12000)*250 No branching. No IF statements. Excel simply runs the calculation and returns the result. That’s the gist of Boolean logic. It can be a great great way to unwind and simplify complex formulas.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
