Logical functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Logical

AND function

Excel 2007

This article describes the formula syntax and usage of the AND function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

Syntax

AND(logical1, [logical2], ...)

The AND function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• logical1  Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
• logical2, ...  Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.

Remarks

• The arguments must evaluate to logical values, such as TRUE or FALSE, or the arguments must be arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or references that contain logical values.
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values, the AND function returns the #VALUE! error value.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Create conditional formulas
• FALSE function
• IF function
• Logical functions
• NOT function
• OR function
• TRUE function

Excel > Function reference > Logical

FALSE function

Returns the logical value FALSE.

Syntax

FALSE( )

Remark

You can also type the word FALSE directly onto the worksheet or into the formula, and Microsoft Excel interprets it as the logical value FALSE.

See Also

• AND function
• Logical functions
• NOT function
• TRUE function

Excel > Function reference > Logical

IF function

Excel 2007

Tags  conditional; formula; functions; IF function; lookup; test; VALUE; vlookup

What are tags?

This article describes the formula syntax and usage of the IF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, [value_if_true], [value_if_false])

The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• logical_test  Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
• value_if_true  Required. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.
• value_if_false  Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is omitted (that is, in the IF function, there is no comma following the value_if_true argument), the IF function returns the value 0 (zero).

Remarks

• Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions. (See Example 4 for a sample of the LOOKUP function.)
• If any of the arguments to IF are arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), every element of the array is evaluated when the IF statement is carried out.
• Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

The preceding example demonstrates how you can nest IF statements. In each formula, the fourth IF statement is also the value_if_false argument to the third IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement, and the second IF statement is the value_if_false argument to the first IF statement. For example, if the first logical_test argument (Average>89) evaluates to TRUE, "A" is returned. If the first logical_test argument evaluates to FALSE, the second IF statement is evaluated, and so on. You can also use other functions as arguments.

The letter grades are assigned to numbers, using the following key.

Example 4

In this example, the LOOKUP function is used instead of the IF function because there are thirteen conditions to test. You may find the LOOKUP function easier to read and maintain than the IF function.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• Autosum results of multiple if statements?
• A logical test in the If function for blank, i.e., If blank?

See Also

• Add numbers
• Add, change, or clear conditional formats
• Calculate the average of a group of numbers
• Check if a cell contains text (case-insensitive)
• Check if a number is greater than or less than another number
• Count how often a value occurs
• Count unique values among duplicates
• Create conditional formulas
• Display or hide zero values
• Logical functions

Excel > Function reference > Logical

IFERROR function

Excel 2007

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).).

Syntax

IFERROR(value,value_if_error)

Value   is the argument that is checked for an error.

Value_if_error   is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Remarks

• If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
• If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. See the second example below.

Example: Trapping division errors by using a regular formula

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example: Trapping division errors by using an array formula

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range C2:C4, press F2, and then press CTRL+SHIFT+ENTER.

See Also

• Logical functions

Excel > Function reference > Logical

NOT function

Excel 2007

Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Syntax

NOT(logical)

Logical   is a value or expression that can be evaluated to TRUE or FALSE.

Remark

If logical is FALSE, NOT returns TRUE; if logical is TRUE, NOT returns FALSE.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AND function
• Create conditional formulas
• Logical functions
• OR function

Excel > Function reference > Logical

OR function

Excel 2007

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax

OR(logical1,logical2,...)

Logical1,logical2,...   are 1 to 255 conditions you want to test that can be either TRUE or FALSE.

Remarks

• The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or references that contain logical values.
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values, OR returns the #VALUE! error value.
• You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AND function
• Check if a cell contains text (case-sensitive)
• Create conditional formulas
• Logical functions
• NOT function

Excel > Function reference > Logical

TRUE function

Returns the logical value TRUE.

Syntax

TRUE( )

Remark

You can enter the value TRUE directly into cells and formulas without using this function. The TRUE function is provided primarily for compatibility with other spreadsheet programs.