«

»

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:

  • IF
  • COUNTIFS
  • SUMIFS

But it is also used for conditional formatting. Yes, you can automatically change the color of your cells according the result of a test.

What it a logical test

Logical tests are every where

  • Is my salary higher than my colleague's?
  • Is my rent higher than my neighbour's?
  • Are the quantities in stock now larger than the quantity in stock at the beginning of the month?
  • ...

There is no limit, every case can be twist on a logical test

A logical test it's just a comparison between 2 items.

Construction of a 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 a function IF.

A test in Excel is very simple.

  • Start your test with the equal sign =.
  • Then, write a value or reference to a cell
  • 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

  • = for ties (equals)
  • > greater than
  • >= greater than or equal to
  • < lower than
  • <= lower or equal to
  • <> different from that of

Result return by a test

A test returns 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: 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: 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 (like any formula)
  • The second is the logical test

Example 3: Cell 1 is greater than Cell 2

To compare the value of one cell with another, we can do 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 applied to conditional formatting.

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.

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


Leave a Reply

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