When you work in Excel, sometimes you may want to calculate data only if a particular condition is met. You can achieve this by using logical operators (also known as comparison operators). Excel provides six of them. This tutorial will provide you with a lot of information on how to use comparison operators inside Excel formulas.
Logical operators overview
As I wrote at the beginning, the logical operators are also called comparison operators. But there is also another name for them – they are called Boolean operators because the result of the comparison always returns TRUE or FALSE.
They work not only with numbers but also with text and dates.
The following table shows the six operators used in Excel and describes how it works.
|equal to||=||A1=B1||Returns TRUE only if A1 is equal to A2|
|greater than||>||A1>B1||Returns TRUE only if A1 is greater than A2|
|less than||<||A1<B1||Returns TRUE only if A1 is less than A2|
|greater than or equal to||>=||A1>=B1||Returns TRUE only if A1 is greater or equal to A2|
|less than or equal to||<=||A1<=B1||Returns TRUE only if A1 is less or equal to A2|
|not equal to||<>||A1<>B1||Returns TRUE only if A1 is different than A2|
Here are a few examples showing comparison operators in action.
As I wrote in the overview, comparison operators also work with text. What you have to remember, is that Excel treats the text as case-insensitive. If you want to treat uppercase and lowercase characters as a different one, you can use the EXACT function with the following.
Here, are some examples.
In cell A10 there is number one formatted as text and then compared with a string. in cell A11 there is a number formatted as a number and then compared to a string. In both cases, each comparison operator returns the same result.
Excel compares text strings in the way that the characters later in the alphabet are considered larger than those that are earlier. So a < t, g < n, etc.
Let’s take a look at how the text comparison works. In the example below, there are three comparisons.
C2: Excel checks the first letter in cell A2 and compares it to the first letter in cell B2. The letter a < k, so the formula (A2>B2) returns FALSE.
C3: The first letter in A3 is the same as the first letter in cell B3. The second letter in cell A3 is “bigger” than the second letter in B3, so the formula returns TRUE.
C4: The first and the second letter are the same in both cells. Excel compares the third letter, which is bigger in cell B4, that’s why Excel returns FALSE.
There is also a different way to compare strings. You can choose to compare them by the number of characters.
If you want to compare the length of two text strings, you can use the following formula.
Besides text and numbers, you can also use logical operators with dates. Let’s take a look at the following table.
The result is probably not what you expected. Why the value in cell C2 is FALSE, and why the values in cells C3 and C5 are TRUE?
You have to remember that a date in Excel is not saved as a date, but rather a number starting from the 1st of January 1900 at 12:00 AM. You can read more about storing dates and times in Excel.
To better illustrate this, press Ctrl + ~ in the upper-left corner of the keyboard, just below the ESC button.
Now, you can see why the cells in the C column return those values.
C2: value A2 is a number and B2 is text.
C3: Both values are equal, just formatted differently.
C5: The same values. The value in B5 is formatted as a date with time. The time is 12:00 AM, which is midnight.
If you want to be sure that the string will be converted to date (number), use the DATEVALUE function.
The following examples illustrate how this function works.
B2: The formula compares the date in cell A2 (number 24) to the text string (“1/24/1900”).
C2: This example compares A2 to the mathematical calculation (1 divided by 24, and divided by 1900).
D2: This DATEVALUE function returns 24, which is the same as the value inside A2.
Comparing numbers to booleans
In some programming languages instead of using TRUE and FALSE, boolean values are represented by 1 and 0.
In Excel, you can also modify boolean operators to be treated as 0 (FALSE) and 1 (TRUE) by adding two minutes (-) operators.
Here’s how you can do it.
Adding one minus (-) sign to TRUE will create a negative value. In order to negate the negative value (to create a positive), you have to add the second minus sign.
When it comes to FALSE, you can add only one minus because there is no negative 0. But I think it’s a good standard to add also two minutes, so you won’t make a mistake.
The NOT function with logical operators
The NOT function will negate every result. The function will return TRUE if the result is FALSE and FALSE if the result is TRUE.
Notice, that =NOT(A2=B2) works the same way as =A2<>B2 and =NOT(A2>B2) returns the same result as =A2<=B2.