Build a Logical Test in Excel

Build a Logical Test in Excel
Last Updated on 04/06/2024
Reading time: 3 minutes

What is a logical test in Excel

Logical tests are a key point of Excel. You found them in many situations

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

A logical test is just a comparison between 2 items 😉

What is a logical test

Construction of a logical test in Excel

A logical test can be written directly in a cell. It's not necessary to use the IF function.

  1. Start your test with the equals sign ( = ).
  2. Then, add a value or cell reference
  3. Write the comparison operator you want (see below)
  4. Finish your test by adding another cell or another value

For instance, we want to know if a task is still in progress or Closed. We can write the test as follows.

Build a test to find the tickets closed

Comparison operators

To create a logical test in Excel, you can use one of the following symbols

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

Training online

Examples of logical tests

In the previous example, we 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

Logical Test for Ages 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 letters instead of customizing your date format. Because a date is a number in Excel, we can create this test to check whether a cell contains a date.

=ISNUMBER(B2)

Logical Test if a cell content a date

If you have this problem, you can convert a text date to a numerical date with the DATEVAL function.

Convert the result to 1 or 0

There is a trick for converting the value TRUE or FALSE to 1 or 0. This article details it.

Use conditional formatting

Conditional formatting is a goldmine for customizing and improving your Excel documents. Conditional formatting allows us to change the color of cells according to their values automatically. And, of course, logical tests help you to create your rules to change the format according to the result of the test.

5 Comments

  1. PRASANTHA
    06/01/2023 @ 22:33

    Hi All,
    I would like to use a dynamic formula in a cell for a logical test in IF condition. Is it possible?

    A1 has the test condition, "<=5".
    A5 is my actual value. I want to try something like this:

    =IF(CONCAT(A5,A1),0,1)

    Intended to derive, if my value is greater than 5 (this comes dynamic), display 0.

    Reply

  2. Amit
    12/07/2020 @ 19:09

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

    Reply

  3. Waqas Shahid
    30/01/2020 @ 20:27

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

    Reply

  4. Paul
    14/11/2018 @ 08:34

    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

    Reply

    • Guest
      02/12/2018 @ 17:50

      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Build a Logical Test in Excel

Reading time: 3 minutes
Last Updated on 04/06/2024

What is a logical test in Excel

Logical tests are a key point of Excel. You found them in many situations

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

A logical test is just a comparison between 2 items 😉

What is a logical test

Construction of a logical test in Excel

A logical test can be written directly in a cell. It's not necessary to use the IF function.

  1. Start your test with the equals sign ( = ).
  2. Then, add a value or cell reference
  3. Write the comparison operator you want (see below)
  4. Finish your test by adding another cell or another value

For instance, we want to know if a task is still in progress or Closed. We can write the test as follows.

Build a test to find the tickets closed

Comparison operators

To create a logical test in Excel, you can use one of the following symbols

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

Training online

Examples of logical tests

In the previous example, we 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

Logical Test for Ages 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 letters instead of customizing your date format. Because a date is a number in Excel, we can create this test to check whether a cell contains a date.

=ISNUMBER(B2)

Logical Test if a cell content a date

If you have this problem, you can convert a text date to a numerical date with the DATEVAL function.

Convert the result to 1 or 0

There is a trick for converting the value TRUE or FALSE to 1 or 0. This article details it.

Use conditional formatting

Conditional formatting is a goldmine for customizing and improving your Excel documents. Conditional formatting allows us to change the color of cells according to their values automatically. And, of course, logical tests help you to create your rules to change the format according to the result of the test.

5 Comments

  1. PRASANTHA
    06/01/2023 @ 22:33

    Hi All,
    I would like to use a dynamic formula in a cell for a logical test in IF condition. Is it possible?

    A1 has the test condition, "<=5".
    A5 is my actual value. I want to try something like this:

    =IF(CONCAT(A5,A1),0,1)

    Intended to derive, if my value is greater than 5 (this comes dynamic), display 0.

    Reply

  2. Amit
    12/07/2020 @ 19:09

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

    Reply

  3. Waqas Shahid
    30/01/2020 @ 20:27

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

    Reply

  4. Paul
    14/11/2018 @ 08:34

    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

    Reply

    • Guest
      02/12/2018 @ 17:50

      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *