There are many ways to add logical decision-making to your Excel spreadsheets. In addition to the standard logical operators in Excel (=, <, >, <>, <=, >=), Excel has compound logical functions which let you evaluate multiple conditions at once. The AND, OR, XOR, and NOT functions in Excel let you evaluate many logical conditions and simply return TRUE or FALSE depending on the function. Because they return TRUE or FALSE, these functions are commonly used with IF functions.
The AND function in Excel evaluates one or more logical conditions to determine whether ALL of them are true. It takes one or more conditions as its arguments separated by commas, and returns TRUE if ALL of the conditions are true. Otherwise, it returns FALSE.
The AND function only requires one argument, but can take more (up to 255). The arguments can be conditions, numbers and text, and cell references. If a cell reference argument is empty, the function simply ignores it. If an argument is a number, it will treat zero as FALSE and any non-zero number (even negatives) as TRUE.
=AND(1<2, "hello"<>"goodbye") evaluates to TRUE because both conditions are true: 1 is less than 2, and "hello" is not equal to "goodbye".
=AND(1<2, 0) returns FALSE because all arguments must be TRUE for the AND function to return true. While the first argument (1<2) is true, the second argument, 0, evaluates to FALSE, so the AND function must return FALSE.
The OR function in Excel evaluates one or more logical conditions to determine whether at least ONE of them is true. It takes one or more conditions as its arguments separated by commas and returns TRUE if at least one of the arguments is true. Otherwise, if all arguments are false, it returns FALSE.
The OR function requires only one argument but can take anywhere up to 255 in most versions of Excel. Like the AND function, the arguments can be conditions, numbers, cell references, even other AND/OR functions. If an argument is a number, it will treat zero as FALSE and any non-zero number as TRUE. It will also ignore blank cell references.
The OR function evaluates each argument and returns TRUE if at least one of the arguments is true.
=OR(1<2, 1) returns TRUE because both arguments are true. The first argument (1<2) is true, and Excel treats the number 1 as true because it is not zero.
=OR(1>2, 1) is also TRUE. The first argument (1>2) is false, but that's okay because the second argument (1) is treated as TRUE because it's not zero. As long as one of the arguments is true, OR will return true.
=OR(1>2, 0) will return FALSE because all of its arguments are false.
The XOR function in Excel is the Exclusive OR function which, like the OR function, takes two conditions and evaluates whether one of them is true. But unlike the OR function, XOR returns TRUE if ONLY ONE argument is true. It returns FALSE if both arguments are true, or if both of the arguments are false. If XOR has more than two arguments, it returns TRUE if an ODD number of its arguments are TRUE, and FALSE if an EVEN number of its arguments are TRUE.
The XOR function also only requires one argument but can take up to 255 in most recent versions of Excel. Like the AND and OR functions, it can take logical conditions, numbers, text, other logical functions, and cell references as its arguments. If a cell reference is blank, XOR will ignore it, and zero will be treated as FALSE while any non-zero number will be TRUE.
=XOR(1=1, "hello"="goodbye") will return TRUE because only one argument is true. The first argument (1=1) is obviously true, but the second ("hello"="goodbye") is clearly false. Because only one argument is true, XOR returns TRUE.
=XOR(1=1, "hello" <> "goodbye") returns FALSE because multiple arguments are true. The first (1=1) is true, as is the second because "hello" is not equal to (<>) "goodbye" so XOR will return FALSE. XOR will only return TRUE if ONE of the arguments is true.
=XOR(1=2, "hello"="goodbye") also returns FALSE because none of the arguments is true. The first (1=2) is false as is the second ("hello"="goodbye").
The NOT function in Excel is perhaps one of the most simple. It takes a single logical argument and returns TRUE or FALSE. It returns the opposite of the argument, so if the argument is true, NOT will return false, and if the argument is false, NOT will return true.
=NOT(1=1) would return FALSE, because the argument is true.
=NOT(1=2) would return TRUE, because the argument is false.
The NOT function is particularly useful when you're interested in excluding some property in Excel. For example, say you are looking at a spreadsheet of homes in Excel, and the B column contains the home's city.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Home # | City | Price | ||
| 2 | Home 1 | Memphis | 300,000 | ||
| 3 | Home 2 | Minneapolis | 250,000 | ||
| 4 | Home 3 | Atlanta | 200,000 |
To exclude any homes in Minneapolis, you could write the following function (starting with row 2) and copy it all the way down the column:
This will return TRUE for any home where the city is not Minneapolis, so it will return true for Home 1 and Home 3, but will return false for Home 2.
Now imagine you only want to exclude single-family homes in Minneapolis, but keep other home types. City is still in column B, while home type is in column C.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Home # | City | Home Type | ||
| 2 | Home 1 | Minneapolis | Apartment | ||
| 3 | Home 2 | Minneapolis | Single-Family | ||
| 4 | Home 3 | Atlanta | Single-Family |
Because the argument (an AND function) returns TRUE only for single-family homes in Minneapolis, this function will return TRUE for any home that is not a single-family home in Minneapolis. It will return true for Home 1 and Home 3, and will only return false for Home 2.
Suppose you love watching new movies, and your favorite movies were made after 2015. Suppose you also love movies that star Will Ferrell and John C. Reilly. But there's a catch: You hate movies that star Will Ferrell and John C. Reilly that were made after 2015. In the table below, where column B is lead actor, column C is supporting actor, and column D is year, how would you find movies that were made after 2015, and movies with the two co-stars mentioned above, but not movies with the two co-stars made after 2015?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Title | Lead Actor | Supporting Actor | Year |
| 2 | Step Brothers | Will Ferrell | John C. Reilly | 2008 |
| 3 | Uncut Gems | Adam Sandler | Idina Menzel | 2019 |
| 4 | Anchorman | Will Ferrell | Christina Applegate | 2004 |
| 5 | Holmes & Watson | Will Ferrell | John C. Reilly | 2018 |
You could use the following formula:
Step Brothers: TRUE because AND(B2="Will Ferrell", C2="John C. Reilly") is TRUE while D2>2015 is FALSE.
Uncut Gems: TRUE because AND(B3="Will Ferrell", C3="John C. Reilly") is FALSE while D3>2015 is TRUE.
Anchorman: FALSE because both conditions: AND(B4="Will Ferrell", C4="John C. Reilly") as well as D4>2015 are FALSE
Holmes & Watson: FALSE because both conditions are true, thus it is a movie with Will Ferrell and John C. Reilly which was made after 2015.