A lot of work in Excel involves comparing data in different cells. When you make a comparison between two values, you want to know one of these things:

• Is value A equal to value B (A=B)
• Is A greater than B (A>B)
• Is A less than B (A<B)
• Is A greater than or equal to B (A>=B)
• Is A less than or equal to B (A<=B)
• Is A not equal to B (A<>B)

These are called logical or boolean operators because there can only be two possible answers in any given case - TRUE or FALSE.

Using Logical Operators in your formulas

Excel is very flexible in the way that these logical operators can be used. For example, you can use them to compare two cells, or compare the results of one or more formulas. For example:

• =A1=A2
• =A1=(A2*5)
• =(A1*10)<=(A2/5)

As these examples suggest, you can type these directly into a cell in Excel and have Excel calculate the results of the formula just as it would do with any formula. With these formulas, Excel will always return either TRUE or FALSE as the result in the cell.

A common use of logical operators is found in Excel's IF function (you can read more about the IF function here). The IF function works like this:

• =IF(logical_test,value_if_TRUE,value_if_FALSE)

In essence, the IF function carries out a logical test (the three examples above are all logical tests) and then return the appropriate result depending on whether the result of the test is true or false. For example:

• =IF(A1>A2,"Greater than","Less than")
• =IF(A1>A2,A1*10%,A1*5%)

However, you don't always need to use an IF formula. Here's a version of this formula that uses a logical operator, and also demonstrates another useful feature of logical operators in general:

• =(A1>A2)*(A1*10*)+(A1<=A2)*(A1*5%)

It looks confusing, but in fact it is very logical (excuse the pun). However, it helps to know that in Excel, TRUE is the same as 1, and FALSE is the same as 0.

So, in this example:

• If A1>A2 is TRUE, then the formula will multiple (A1*10%) by 1.
• Because A1>A2 is TRUE then A1<=A2 is false, so it will then multiply (A1*5%) by 0.
• It will then add the results together: (A1*10%)*1 + (A1*5%)*0.
• The final result is whatever (A1*10%) equals in the specific example.

Obviously, if A1 is less than A2, then the reverse of this would occur.

Using Multiple Logical Operators

In some cases, you may want to perform more than one comparison as part of your formula. For example:

• (Today is Wednesday) and (Sky is Blue)
• (Today is Wednesday) or (Sky is Blue)
• (Today is Wednesday and (Sky is NOT Blue)
• (Today is Wednesday) or (Sky is NOT Blue)

In Excel, you can use one of three logical functions to construct these formulas:

• AND
• OR
• NOT

The AND function works by performing multiple comparison tests and then returning TRUE if all of the tests were true, and FALSE if one or more of the tests were false. Here are a couple of examples:

• =AND(A1>A2,A1<A3) (if A1 is greater than A2 AND less than A3, then return TRUE otherwise return FALSE)
• IF(AND(A2>A2,A1<A3),"Both are true","At least one is false") (this IF function will return one of the two values depending on whether the AND function returns TRUE or FALSE).

The OR function works in a very similar way to the AND function. However, whereas AND requires that all tests return true, the OR function will return TRUE if only one of the tests return true. For example:

• =OR(A1>A2,A1<A3) (if either A1>A2 OR A1>A3 is true, then return TRUE. If neither are true, return FALSE).
• =IF(OR(A1>A2,A1<A3),"One or both are true","Neither are true")

It is important to note that the AND and the IF functions can both incorporate up to 255 logical tests (my examples here have only used 2). Regardless of the number of tests you include, the same rules apply as they did in my simple examples.

It is also worth noting that you can combine the AND and OR functions in a single formula. For example:

• =AND(OR(A1>A2,A1<A3),A1>A4)

In this example, the AND function will only return TRUE if either (A1>A2 OR A1<A3) AND A1>A4

The final logical function you can use is the NOT function. The NOT function is somewhat self-explanatory - it takes any logical test result and does the opposite. For example:

• =(Sky is Blue) - will return TRUE if the sky is blue, and FALSE if the sky is not blue.
• =NOT(Sky is Blue) will return FALSE if the sky is blue, and TRUE if the sky is not blue.

Note that this example doesn't care what other colors the sky might be!

Of course, you can use the NOT function with the AND, OR and IF functions:

• =NOT(AND(A1>A2,A1<A3)) - if A1>A2 AND A1<A3, then return FALSE
• =AND(NOT(A1>A2),A1<A3) - if A1 is NOT >A2 AND A1<A3 then return TRUE.

Note that writing NOT(A1>A2) is another way of writing (A1<=A2). In this simple example, using a NOT function didn't add much value, but in some cases the NOT function can be very handy.

In summary, a lot of what you do in Excel, particularly once you start using IF functions, involves using logical operators. The logical functions, AND, OR and NOT are a great way to extend your use of logical operators to perform more complex calculations.