Boolean logic in Excel: TRUE/FALSE instead of IF functions

A new version of this article has been published here: http://easy-excel.com/?p=638

Why use the IF function when there is an easier way to do it? If you’re looking for a TRUE or FALSE answer, you don’t need it! Let’s use Boolean logic instead – it will save you a lot of time and struggle.

Let’s look at an example: In this sales report, the sales reps get a bonus if they reach $ 15,000 in sales.

Most people would solve this with an IF statement such as =IF(C5>$C$2,”TRUE”,”FALSE”), but there is an easier way:

=C5>C2

This formula will return TRUE or FALSE. Of course, in order to copy the formula down, we need to lock the reference to the bonus hurdle in C2 with dollar signs (Shortcut: F4):

=C5>$C$2

Getting used to this logic also makes conditional formatting a lot easier! Conditional formatting relies on the TRUE/FALSE logic – if the statement is true, the cell is formatted. All you have to do here is to select all the cells in the bonus column and format them with the very same formula as above.

Click on Conditional Formatting from the Home ribbon and choose New Rule (Shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the formula in the formula field.

Click on the Format button and choose a fill colour or font colour.

That’s all, and here is the result:

Bonus tip: If you want to replace TRUE and FALSE with ones and zeroes, it’s done in a second:

Put the logical statement in brackets and add a zero: =(C5>$C$2)+0

Or put the logical statement in brackets and add a double negative: =- -(C5>$C$2)

This could be a good idea if you want to use the results from column D for further calculations:

Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.

Author: Audun Danielsen

About these ads

5 thoughts on “Boolean logic in Excel: TRUE/FALSE instead of IF functions

  1. Pingback: Create a search field in Excel in 5 minutes « Excel is easy!

  2. Pingback: Find Duplicates and Triplicates in Excel « Excel is easy!

  3. Pingback: Highlight Entire Row In Excel Based On One Cell « Excel is easy!

  4. What if the sales rep reach $15000, the formula above will return “False”. So i think you should add an equal (=) sign in the formula to make it return “True” when the sales are $15000.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s