Logical operators in Excel: equal to, not equal to, greater than, less than

Logical operators in Excel: The key to efficient data analysis

In Excel, many tasks involve comparing data in different cells. To this end, Microsoft Excel provides six logical operators, also known as comparison operators. This tutorial is designed to help you understand the connotation of Excel logical operators and write the most efficient formulas for data analysis.

• Excel logical operators
  • equal
  • Not equal to
  • Greater than/less than/greater than/six equal to/less than equal to
• Common uses of logical operators in Excel

Overview of Excel Logical Operators

Logical operators in Excel are used to compare two values. Logical operators are sometimes called Boolean operators, because in any given case the result of the comparison can only be TRUE or FALSE.

Six logical operators are provided in Excel. The following table explains the role of each operator and illustrates its principles with formula examples.

condition	Operators	Example formula	describe
equal	=	=A1=B1	If the value in cell A1 is equal to the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.
Not equal to		=A1B1	If the value in cell A1 is not equal to the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.
Greater than	>	=A1>B1	If the value in cell A1 is greater than the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.
Less than		=A1	If the value in cell A1 is smaller than the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.
Greater than or equal to	>=	=A1>=B1	If the value in cell A1 is greater than or equal to the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.
Less than or equal to		=A1	If the value in cell A1 is less than or equal to the value in cell B1, the formula returns TRUE; otherwise, it returns FALSE.

The following figure shows the results returned by the "equal", "not equal", "greater than" and "less than" logical operators:

The above table seems to cover everything and there seems to be nothing more to discuss. But in reality, each logical operator has its own characteristics, and understanding these characteristics can help you take advantage of the power of Excel formulas.

Using the "equal" logical operator in Excel

The "equal" logical operator (=) can be used to compare the results returned by all data types - numbers, dates, text values, boolean values, and other Excel formulas. For example:

=A1=B1	If the values ​​in cells A1 and B1 are the same, TRUE is returned; otherwise, FALSE is returned.
=A1="oranges"	If cell A1 contains the word "oranges", TRUE is returned; otherwise, FALSE is returned.
=A1=TRUE	If cell A1 contains a Boolean TRUE, TRUE is returned; otherwise, FALSE is returned.
=A1=(B1/2)	If the number in cell A1 is equal to the quotient of B1 divided by 2, TRUE is returned; otherwise, FALSE is returned.

Example 1. Use the "equal" operator with date

You might be surprised to find that the "equal" logical operator cannot compare dates as easily as numbers. For example, if cells A1 and A2 contain the date "January 12, 2014", the formula =A1=A2 will return TRUE, which is exactly what it should return.

However, if you try to use =A1=12/1/2014 or =A1="12/1/2014" , the result will be FALSE. A little unexpected, right?

The key is that Excel stores dates as numbers starting January 1, 1900, which is stored as 1. "January 12, 2014" is stored as 41974. In the above formula, Microsoft Excel interprets "12/1/2014" as a normal text string, and since "12/1/2014" does not equal 41974, it returns FALSE.

To get the correct result, you must always wrap the date in the DATEVALUE function as shown below =A1=DATEVALUE("12/1/2014")

Note: The DATEVALUE function also needs to be used with other logical operators, as shown in the following example.

The same approach should also be used when you use Excel's equality operator in logic testing of IF functions. You can find more information in this tutorial and some formula examples: Use Excel IF functions to process dates.

Example 2. Use the "equal" operator with text value

Using Excel's "equal" operator with text values ​​does not require any extra tricks. The only thing you need to note is that the "equal" logical operator in Excel is case-insensitive , which means that case differences are ignored when comparing text values.

For example, if cell A1 contains the word " oranges " and cell B1 contains " Oranges ", the formula =A1=B1 will return TRUE.

If you want to consider their case differences when comparing text values, you should use the EXACT function instead of the "equal" operator. The syntax of the EXACT function is very simple:

EXACT(text1, text2) where text 1 and text2 are the values ​​to be compared. If the value is exactly the same, including case, Excel returns TRUE; otherwise, it returns FALSE. When you need to compare text values ​​case sensitively, you can also use the EXACT function in the IF formula, as shown in the screenshot below:

Note: If you want to compare the lengths of two text values, you can use the LEN function, such as =LEN(A2)=LEN(B2) or =LEN(A2)>=LEN(B2) .

Example 3. Comparing Boolean values ​​and numbers

It is generally believed that in Microsoft Excel, the Boolean TRUE is always equal to 1 and FALSE is equal to 0. However, this is only partially correct, the keyword is "always" or more accurately "not always" :)

When writing a logical expression that compares boolean values ​​and numbers, you need to make it clear that Excel should treat non-numeric boolean values ​​as numbers. You can do this by adding a double minus sign before a boolean value or cell reference, such as =A2=--TRUE or =A2=--B2 .

The first minus sign (technically called the unary operator) casts TRUE/FALSE to -1/0, respectively, and the second unary operator negates these values, converting them to 1 and 0. This may be easier to understand by looking at the screenshot below:

Note: When correctly comparing numeric values ​​with boolean values ​​using other logical operators such as "not equal to", "greater than", or "less than", you should add double unary operators before the boolean values.

When using logical operators in complex formulas, you may also need to add double unary operators before each logical expression that returns TRUE or FALSE as the result. Here is an example of such formula: SUMPRODUCT and SUMIFS in Excel.

Using "not equal" logical operator in Excel

When you want to make sure that the value of a cell is not equal to the specified value, you can use Excel's "not equal to" operator (). The use of the "not equal" operator is very similar to the "equal" operator we just discussed.

The result returned by the "not equal" operator is similar to the result produced by the Excel NOT function, which inverts the value of its parameters. The following table provides some examples of formulas.

Not equal to operator	NOT function	describe
=A1B1	=NOT(A1=B1)	If the values ​​in cells A1 and B1 are different, TRUE is returned; otherwise, FALSE is returned.
=A1"oranges"	=NOT(A1="oranges")	Returns TRUE if cell A1 contains any value other than "oranges", and returns FALSE if cell A1 contains "oranges" or "ORANGES" or "Oranges", etc.
=A1TRUE	=NOT(A1=TRUE)	If cell A1 contains any value other than TRUE, TRUE is returned; otherwise, FALSE is returned.
=A1(B1/2)	=NOT(A1=B1/2)	If the number in cell A1 is not equal to the quotient of B1 divided by 2, TRUE is returned; otherwise, FALSE is returned.
=A1DATEVALUE("12/1/2014")	=NOT(A1=DATEVALUE("12/1/2014"))	If A1 contains any value except December 1, 2014, TRUE is returned regardless of date format; otherwise, FALSE is returned.

greater than, less than, greater than or equal to, less than or equal to

You can use these logical operators in Excel to check the comparison results of one number to another. Microsoft Excel provides 4 comparison operators whose names are self-explanatory:

• Greater than (>)
• Greater than or equal to (>=)
• Less than (
• Less than or equal to (

In most cases, the Excel comparison operator is used with numbers, dates, and time values. For example:

=A1>20	If the number in cell A1 is greater than 20, TRUE is returned; otherwise, FALSE is returned.
=A1>=(B1/2)	If the number in cell A1 is greater than or equal to the quotient of B1 divided by 2, TRUE is returned; otherwise, FALSE is returned.
=A1	If the date in cell A1 is less than December 1, 2014, TRUE is returned; otherwise, FALSE is returned.
=A1	Return TRUE if the number in cell A1 is less than or equal to the sum of the values ​​in cell B1:D1; otherwise, return FALSE.

Handle text values ​​using Excel comparison operator

In theory, you can also use the "greater than", "greater than equal to" operators and their "little than" corresponding operators with text values. For example, if cell A1 contains " apples " and B1 contains " bananas ", guess what will the formula =A1>B1 return? Congratulations to those who bet on FALSE :)

When comparing text values, Microsoft Excel ignores their case and compares values ​​symbolically, "a" is considered the smallest text value and "z" is considered the largest text value.

So when comparing the values ​​of " apples " (A1) and " bananas " (B1), Excel starts with their first letters "a" and "b", and since "b" is greater than "a", formula =A1>B1 returns FALSE.

If the first letter is the same, then the second letter is compared, if they are the same, Excel continues to compare the third, fourth letters, and so on. For example, if A1 contains " apples " and B1 contains " agave ", then formula =A1>B1 will return TRUE because "p" is greater than "g".

At first glance, using the comparison operator with text values ​​seems to have little practical significance, but you never know what you might need in the future, so this knowledge might be helpful to some people.

Common uses of logical operators in Excel

In actual work, Excel logic operators are rarely used alone. Agree, the boolean values ​​TRUE and FALSE they return are very correct (pun), but not much meaning. To get more meaningful results, you can use logical operators as part of an Excel function or conditional formatting rule, as shown in the example below.

1. Use logical operators in the parameters of Excel function

When it comes to logical operators, Excel is very loose and allows them to be used in the parameters of many functions. One of the most common uses is in Excel IF functions, where the comparison operator can help build logical tests, and the IF formula will return the corresponding result based on whether the test result is TRUE or FALSE. For example:

=IF(A1>=B1, "OK", "Not OK")

This simple IF formula returns OK if the value in cell A1 is greater than or equal to the value in cell B1, otherwise it returns "Not OK".

Here is an example:

=IF(A1B1, SUM(A1:C1), "")

This formula compares the values ​​in cells A1 and B1, and if A1 is not equal to B1, it returns the sum of the values ​​in cell A1:C1, otherwise it returns an empty string.

Excel logic operators are also widely used in special IF functions, such as SUMIF, COUNTIF, AVERAGEIF and their complex corresponding functions, which return results based on specific conditions or multiple conditions.

You can find a large number of formula examples in the following tutorial:

• Using IF functions in Excel
• How to use SUMIF in Excel
• Excel SUMIFS and SUMIF and multiple conditions
• Using COUNTIF in Excel
• Excel COUNTIFS and COUNTIF and multiple conditions

2. Use Excel logic operators in mathematical calculations

Of course, Excel functions are very powerful, but you don't always need to use them to achieve the desired results. For example, the following two formulas return the same result:

IF function: =IF(B2>C2, B2*10, B2*5)

Formulas that use logical operators: =(B2>C2)*(B2*10) (B2

I guess the IF formula is easier to understand, right? It tells Excel to multiply the value in cell B2 by 10 if B2 is greater than C2, otherwise multiply the value in B1 by 5.

Now, let's analyze the role of the second formula using the "greater than" and "less than equal to" logical operators. Understand that in mathematical calculations, Excel does equate Boolean TRUE to 1, and equating FALSE to 0 is helpful. Keeping this in mind let's see what each logical expression actually returns.

If the value in cell B2 is greater than the value in cell C2, expression B2>C2 is TRUE, thus equal to 1. On the other hand, if B2 is less than or equal to C2, then expression B2>C2 is FALSE(0), and expression B2

Since any number multiplied by zero equals zero, we can remove the second part of the formula after the plus sign. And since any number multiplied by 1 equals that number, our complex formula becomes simple =B2*10, which returns the product of B2 times 10, which is exactly what the above IF formula does :)

Obviously, if the value in cell B2 is smaller than the value in cell C2, the value of expression B2>C2 is FALSE(0), and the value of B2

3. Logical operators in Excel conditional format

Another common use of logical operators is in Excel conditional formatting, which allows you to quickly highlight the most important information in a spreadsheet.

For example, the following simple rule highlights selected cells or entire rows in the worksheet based on the values ​​in column A:

Less than (orange): =A1

Greater than (green): =A1>20

For detailed step-by-step instructions and rule examples, see the following article:

• Excel conditional format formula
• How to change row color based on cell value
• Two ways to change background color according to cell value
• How to highlight every other row in Excel

As you can see, using logical operators in Excel is intuitive and simple. In the next article, we will learn the ins and outs of Excel logic functions that allow multiple comparisons to be performed in one formula. Please continue to pay attention and thank you for reading!

The above is the detailed content of Logical operators in Excel: equal to, not equal to, greater than, less than. For more information, please follow other related articles on the PHP Chinese website!