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