Tag Archives: logical testing

Excel Functions and Formulas Hints and Tips

Logical testing in Excel: A basic primer

In this article, I want to take you through the basics of logical testing in Microsoft Excel.   Logical testing forms the basis of many complex spreadsheet models, and it’s also a large part of some of the more powerful Excel functions.  A good grasp of logical testing and how it works can help you out no end as you learn more about formulas and functions.

So, what do I mean by logical testing?

A logical test is essentially a question that you ‘ask’ Excel.  In fact, it’s not really a question at all, it’s more of a bold statement which Excel then evaluates and either agrees with, or argues with us about.  Let’s use an example.

Is 5 equal to 10?  The answer is obviously no.  Here’s how to ask the same question to Excel.  In any cell, type in the following:-

=5=10

And press the Enter key.  The cell will now display a FALSE result, confirming that 5 is indeed not equal to 10.  The logical test (that is, the question ‘is 5 equal to 10’) is posed to Excel in terms of a simple formula.  In this case our formula states that 5 is equal to 10 and, when we press the Enter key, Excel either agrees with our statement, or tells us straight that it’s a pack of lies.

A couple more examples:-

Is 10 less than 20?

Formula:  =10<20
Result: TRUE

Is the value in cell C5 equal to the value in cell D5?

Formula:  =C5=D5
Result:  FALSE

And that’s it, you’ve just learned the basics of logical testing.  I know it’s pretty simple stuff, but I recently did a training course on Excel and a few of the attendees weren’t too clear on what was happening, so I took them through a similar spoken tutorial which seemed to help.  These fundamentals are used all of the time in advanced spreadsheets, and an understanding of how and why they work can really help when using some of the more complex Excel functions.