Logical Functions in Excel

By Laura Gittins

Microsoft Excel has seven different logical functions which you use to test data in your spreadsheets. Like the other Excel functions, you call logical functions with the equals sign. Most of the logical functions take a list of conditions or arguments to evaluate and return a result as data in the cell in which you enter the formula. You may embed several logical functions within another to test data.

AND and OR

The AND function returns true if every condition in the arguments list are true, and false if at least one argument returns false. The OR function returns true if at least one condition returns true and false if every condition returns false. For example, if the value in cell A1 is five, the function calls "=AND(2+2=A1, 10-5=A1)" returns false because the first condition fails, but "=OR(2+2=A1, 10-5=A1)" returns true because the second condition returns true. You may include up to 30 different arguments for each AND and OR function call.


The IF function performs a logical test on a value or expression. It then displays one of two outcome values; one for true and one for false. For example, if the value in cell A1 is 30, the function call "=IF(A1>25,"Yes," "No")" returns true and displays "Yes" because 30 is greater than 25. If the value in cell A1 is 20, the same function call returns false and displays "No." You may embed up to seven IF functions within each other.


The IFERROR function takes two parameters. The first is a value or expression which the function tests to determine if an error exists, the second is a message to display if the expression returns true, or that it finds an error. For example, "=IFERROR(5/1,"Error found")" returns no error, so the result returns is "5," the value of the expression. However, "=IFERROR(5/0,"Error found")" will return "Error found" as dividing by zero returns an error.


The NOT logical function takes the values of its arguments and reverses them, primarily with true or false expressions. For example, if the value in cell A1 is 10, the function call "=NOT(A1>5)" returns false because, while the expression itself returns true, NOT reverses it to return false. Used with other logical functions, the NOT logical function helps you determine when one value is not equal to another. For example, "=IF(NOT(A1=A2), "Valid", "Invalid")" returns true and displays "Valid" if the values in cells A1 and A2 are not equal, otherwise it returns false.


The TRUE and FALSE logical functions simply return true and false, respectively. Alone, they are no different from manually typing "TRUE" or "FALSE" into an empty cell. Neither function require any parameters. The syntax for each is "TRUE()" and "FALSE()". These function are meant to test compatibility with other spreadsheet applications.