Exceljet

Quick, clean, and to the point

This video comes from our Excel video training.

How to use boolean logic in Excel formulas

Tags 
Video Transcript 

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:

=IF(sales>10000,500,0)

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:

=(sales>10000)*500

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:

=IF(C5>12000,500+250,IF(C5>10000,500,0))

What about a boolean version?

Well, with boolean logic, we simply write write:

=(C5>10000)*500+(C5>12000)*250

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.

Author 
Dave Bruns
I have been on your emailing list and your tips have made sense to me so I thought that would take the next step and really try and up skill myself with excel. - Maara
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course