What is a Logical Test in Excel

When should you create a logical test?

Creating a logical test is THE starting point for these 3 important functions 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?
  • ...

A logical test is just a comparison between 2 items.

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.

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

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

=8>1

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

Result returned by a logical test

A test can return one of 2 values: TRUE or FALSE (the test is correct or the test is not correct)

Let's return to our formula =8>1 and the result is, of course, TRUE (yes, 8 is greater than 1)

Now, if you reverse the test, the result is different. =8<1 is now FALSE.

But it is possible to return 1 when the test is TRUE or 0 when the test is FALSE.

Simply multiply your test by 1 😉😍😃👍

Examples of logical tests

Example 1: Is the value greater than 10?

If you want to know if the cell contents are greater than a specific value, the formula is

=A2>10

It's so easy 😎😃😍

Example 2: 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<>""

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

On the contrary, if you want to know if a cell is empty, the test is written as

=A2=""

Don't be surprised by the 2 equal signs

  • The first one is to start your test (the same as for any formula)
  • The second is the logical test

Example 3: Is cell 1 is greater than cell 2?

To compare the value of one cell with another, we perform the following test:

=A2<B2

Use with conditional formatting

There is nothing complex about creating a test in Excel. But it's application is more interesting when conditional formatting is applied to the result.

Conditional formatting allows us to automatically change the color of cells according to their values.

So logical tests are essential in understanding Excel. If you don't use these tests, you will not fully master Excel.

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

Related posts


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


2 comments

    • 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.