When should you create a logical test?
Creating a logical test is THE starting point for these 3 important functions in Excel:
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
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
Let's return to our formula =8>1 and the result is, of course, TRUE (yes, 8 is greater than 1)
Simply multiply your test by 1 😉😍😃👍
Examples of logical tests
Example 1: value greater than 10
It's so easy 😎😃😍
Example 2: Cell empty or not?
The 2 double quotes is the code for an empty cell (it's a string with nothing in between).
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
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.