What is a logical test
Logical tests are everywhere.
- 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 😉
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
Logical symbols you can use
To create a test, 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
Click on the following image to open the exercise
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.
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
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
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
For instance, a common mistake in Excel is to write the month in letters instead of customizing your date format. So, because a date is a number in Excel, we can create this test to check if a cell contains a date or not.
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 to convert the value TRUE or FALSE to 1 or 0. The detail in this article.
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.