MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use Boolean Logic


August 05, 2019 - by Bill Jelen

Excel Use Boolean Logic. Photo Credit: Fancycrave at Unsplash.com

I always cover IF in my seminars. And I always ask how people would solve the two-conditions problem. The results are often the same: 70–80% of people use nested IF, and 20–30% use AND. Just one time, in Virginia, a woman from Price Waterhouse offered the formula shown below:


The formula starts out calculating a bonus with =B4*0.02. You then multiply that result with *(B4>20000)*(C4>0.5).

It works. It gives the same answer as the other formulas. Calculate the bonus .02*B4. But then multiply that bonus by logical tests in parentheses. When you force Excel to multiply a number by True or False, the True becomes 1, and the False becomes 0. Any number times 1 is itself. Any number times 0 is 0. Multiplying the bonus by the conditions ensures that only rows that meet both conditions are paid.

It is cool. It works. But it seems confusing when you first see it. My joke in my seminar is, “If you are leaving your job next month and you hate your co-workers, start using this formula.”

Title Photo: Fancycrave at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.