Logical operators in Excel are symbols that compare two values. The logical operation, or comparison, is a statement that is either true or false, so they are often used in the first part of IF statements.
There are six logical operators in Excel:
Let's take a look at each one in detail.
The greater than symbol states that the value on its left is greater than the value on its right.
5>6 (false)This logical operation would be false, because 5 is not greater than 6.
6>5 (true)This logical operation would be true, because 6 is greater than 5.
5>5 (false)This expression would be false because 5 is exactly equal to 5, so it would be false to say that 5 is greater than 5.
Logical operators also work to compare text, with letters later in the alphabet considered "greater than" letters in the beginning of the alphabet.
"Cat">"Dog" (false)This logical operation is false because "D" comes after "C" alphabetically, so "Cats" is not greater than "Dogs" in Excel.
"Dog">"Cat" (true)This is a true statement because "Dog" comes after "Cat" alphabetically.
The less than symbol states that the value on its left is less than the value on its right.
9<10 (true)This statement is true because 9 is less than 10.
10<9 (false)This statement is false because 10 is not less than 9.
9<9 (false)This statement would also be false because 9 is equal to 9, so it would be incorrect to say 9 is less than 9.
"Cat"<"Dog" (true)This statement is true because "Cat" comes before "Dog" in the alphabet.
"Dog"<"Cat" (false)This statement is false because "Dog" comes after "Cat" in the alphabet and is thus considered greater.
Many people have trouble remembering the difference between the greater than and less than operators because they are so similar. One way to remember this is the "Alligator Rule." Pretend the operator is the mouth of a hungry alligator. The alligator will try to eat whichever value is greater, so if the open part of the operator is facing the greater value, the statement is true.
The equal to operator states that the values on each side of it are exactly equal. This applies to both numerical and text values. If they are not the same, the statement will be false.
5=5 (true)This statement is true because 5 is exactly equal to 5. In other words, they are the same value.
5=5.000001 (false)This statement is false because the values are not exactly equal, even if they are very close.
"Dog"="Dog" (true)This statement is true because the two strings of text are exactly the same.
"Dog"="Dogs" (false)This statement is false because the words are not exactly the same, even though they are very similar.
(3/4)=0.75 (true)This statement is true even though the values don't look the same. 3/4 and 0.75 are numerically equivalent values, so they are equal to one another.
The equal to operator in Excel is not case-sensitive when used on text. This means that it considers two strings of text to be equal as long as the letters are the same regardless of capitalization.
"dogs and cats"="DoGs AnD cAtS" (true)This statement is true because the equal to operator is not case-sensitive, so it considers the two strings of text to be equivalent, even though they have differing capitalization. To test whether two strings of text are exactly the same (including capitalization) use the EXACT function.
The greater than or equal to operator states that (you guessed it) the value on its left is greater than or equal to the value on its right.
10>=9 (true)This statement is true because 10 is greater than 9.
10>=10 (true)This statement is also true because 10 is equal to 10, and the statement says that 10 is greater than or equal to 10.
10>=11 (false)This one is false because 10 is not greater than 11 or equal to 11, it is simply less than 11.
The less than or equal to operator, as you've probably guessed, asserts that the value on its left is less than or equal to the value on its right.
10<=11 (true)This statement is true because 10 is less than 11.
10<=10 (true)This statement is true because 10 is not less than 10, but is equal to 10.
11<=10 (false)This statement is false because 11 is neither less than nor equal to 10. it is greater than 10.
Finally, we have the Not Equal To operator, which states that the value on its left is not equal to the value on its right. In other words, the operator asserts that they are different values.
You may have noticed that the Not Equal To operator is composed of the less than and greater than symbols put together. This is because saying "x is less than or greater than y" is the same as saying "x is not equal to y."
10<>9 (true)This statement is true because 10 is not equal to 9.
"Dog"<>"Cat" (true)This statement is true because the text "Dog" is not equal to the text "Cat."
10<>10 (false)This statement is false because 10 is indeed equal to 10, so it would be false to say that 10 is not equal to 10.
(3/4)<>0.75 (false)This statement is also false because although these values look different, numerically they are equal, so it would be incorrect to say that 3/4 is not equal to 0.75.
In Excel, logical operators form the basis for all logic and decision making when writing a formula. These operators are used in the IF function, SUMIF function, COUNTIF function, and others.
Say you had the following spreadsheet and cell A1 contained the number of days in a given year. You wanted to write an IF statement to display the text "Leap Year!" if there are 366 days in the year, and display the text "Regular Year" if there are 365 days in the year. There are numerous ways this can be accomplished with logical operators.
=IF(A1=366, "Leap Year!", "Regular Year")This formula says that if the number of days in the year is equal to 366, then display the text "Leap Year!" and otherwise display the text "Regular Year."
=IF(A1>365, "Leap Year!", "Regular Year")This formula says that if the number of days in the year is greater than 365, then display the text "Leap Year!" and otherwise display the text "Regular Year."
=IF(A1<>365, "Leap Year!", "Regular Year")This formula says that if the number of days in the year is not equal to 365, then display the text "Leap Year!" and otherwise display the text "Regular Year."
As you can see, there are numerous ways to use logical operators to accomplish the same task in Excel. The best way to learn them is to get your hands dirty and try writing a few of your own.