Build a Logical Test in Excel

What it a logical test

Logical tests are everywhere.

  • Is my salary higher than my colleague's?
  • Is my rent higher than my neighbour's?
  • Is the quantity in stock larger now than at the beginning of the month?
  • Does a ticket is closed or not?
  • ...

A logical test is just a comparison between 2 items 😉

What is a logical test

Construction of a logical test

You can create a logical test in a cell WITHOUT using the IF function.

In fact, it is the opposite. A test is used in an IF function.

A test in Excel is very simple.

  1. Start your test with the equals sign =.
  2. Then add a value or cell reference
  3. Then the logical symbol (see below)
  4. Then another cell or another value

For instance, write the following formula in a cell to see the result

=C2="Closed"

Build a test to find the tickets closed

Logical symbols you can use

To create a test, you can use one of the following symbol

  • = equal to
  • > greater than
  • >= greater than or equal to
  • < lower than
  • <= lower or equal to
  • <> not equal to

Examples of logical tests

On the previous example, we have created a test with the string "Closed". But you can build a test with numerical values or formulas

Example 1: Does the age is greater than 21?

If you want to know if the cell contents are greater than a specific value, like 21, you can write the following test.

=B2>21

Which age is greater than 21

Example 2: Compare 2 cells

You can also create the same test but this time, the value 21 is in the cell G4. So, instead of comparing one cell with one value, you can also create a test between 2 cells

=B2>=$G$4

Age Greater than 21

In this example, we must add dollars to block the reference of the cell G4

Example 3: Is the cell empty or not?

Now, if you want to know if a cell is not empty, you will write the following formula

=A2<>""

Test if a cell is empty

The 2 double quotes is the code for an empty cell (it's a string with nothing in between) 😉

Many functions return TRUE or FALSE

Excel has a collection of functions that return TRUE or FALSE. These functions start with IS

  • ISBLANK
  • ISERROR
  • ISFORMULA
  • ISNA
  • ISNUMBER
  • ISNONTEXT

For instance, a common mistake in Excel is to write the month in letter instead of customise your date format. So, because a date is a number in Excel, we can create this test to check if a cell contents a date or not.

=ISNUMBER(B2)

Test if a cell content a date

Convert the result to 1 or 0

There is a trick to convert the value TRUE or FALSE to 1 or 0. Detail in this article.

Convert logical test by multiply by 1

Use with conditional formatting

Conditional formatting is a goldmine for customizing and improving your Excel documents.

Conditional formatting allows us to automatically change the colour of cells according to their values. And of course, logical tests help you to create your rules to change the format according to the result of the test.

Related posts


Permanent link to this article: https://www.excel-exercise.com/logical-test-excel/

4 comments

Skip to comment form

    • Amit on 12/07/2020 at 19:09
    • Reply

    Great and valuable example to use of logical function to creat logical test..

  1. Very helpful and simple information. Easy to understand. Thanks!

    • Paul on 14/11/2018 at 08:34
    • Reply

    Thank you Frédéric for the above examples.

    I have been surprised to find that logical tests always determine that a text string is > a number.
    e.g. ="ABC">10 calculates as TRUE.
    It doesn't matter what the text string is nor the size of the number, it always calculates as TRUE.

    Is this explainable?

    Regards,
    Paul

      • Guest on 02/12/2018 at 17:50
      • Reply

      For the Excel compare operators (e.g. ), number values are less than text values which are less than logical values. If you were to try ="ABC">FALSE then it would evaluate to FALSE.

Leave a Reply

Your email address will not be published.