This video comes from our Excel video training.
In this video, I'm going to show you the basics of boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements.
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 =B4>30 here, we'll get the boolean result TRUE.
So, B4>30 is a boolean expression - or logical statement - that returns TRUE or FALSE.
If I copy the formula down, we get boolean result for each number.
What's interesting about boolean values is that they have numeric equivalents.
During a math operation, Excel will coerce booleans into numbers, TRUE becomes 1, and FALSE becomes zero.
I can prove this with a formula that simply adds zero 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 zero.
So, how can we use this in Excel?
Let's look at simple example.
Here we have a list of salespeople and sales numbers. Let's say they get a bonus of $500 if they exceed 10000 in sales.
One way to handle that with a formula is to write:
which works fine.
But how could we use boolean logic instead?
Well, sales greater than 10000 is a logical expression that returns TRUE or FALSE.
That means we can just rewrite the formula like this:
So, that's nice. We got rid of the IF statement.
But perhaps a little silly, just to get rid of a single IF statement.
Yes, so let's add a little complexity.
What if there were a special $250 bonus for sales over 12000?
In that case, you might see a nested IF formula like this:
What about a boolean version?
Well, with boolean logic, we simply write write:
No branching. No IF statements. Excel simply runs the calculation and returns the result.
So that's the gist of boolean logic.
Look for opportunities to use it in your formulas.
It can be a great great way to unwind and simplify complex formulas.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
View the discussion thread.
Quick, clean, and to the point.