﻿ IF function

# IF function

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.

## Syntax

IF(logical_test, value_if_true, [value_if_false])

For example:

• =IF(A2>B2,"Over Budget","OK")

• =IF(A4=500,B4-A4,""

 Argument name Description logical_test    (required) The condition you want to test. value_if_true    (required) The value that you want returned if the result of logical_test is TRUE. value_if_false    (optional) The value that you want returned if the result of logical_test is FALSE.

## Examples

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. To see the formula in a formula cell, select the cell and press F2.

Actual Expense

Predicted Expense

\$1,500

\$900

\$500

\$900

\$500

\$925

=IF(A2>B2,"Over Budget","OK")

Because the actual expense of \$1500 (A2) exceeded the predicted expense of \$900 (B2), the result is Over Budget .

=IF(A2<B2,TRUE, IF(A3>B3,"over budget","OK"))

The first IF function is false. Therefore, the second IF statement is calculated and because it too is false, the result is OK.

=IF(A4=500,B4-A4,"")

Because A4 equals 500, the Actual Expense \$500 is subtracted from Predicted Expense \$925 to tell you how much over budget you are. The result is 425. If A4 didn't equal 500, then empty text ("") would be returned.

=IF(A2<B2,TRUE, IF(A3>B3,"over budget","OK"))

The first IF function is false. Therefore, the second IF statement is calculated and because it too is false, the result is OK.

## Common Problems

 Problem What went wrong O (zero) in cell There was no argument for either value_if_true or value_if_False arguments. To see the right value returned, add argument text to the two arguments, or add TRUE or FALSE to the argument. #NAME? in cell This usually means that the formula is misspelled .

## Best practices

 Do this Why Using an IF function within another IF. Up to 64 IF functions can be nested within each other for value_if_true and value_if_false arguments to construct more elaborate tests. Use IF with arrays If any of the arguments to the IF function are arrays, every element of the array is evaluated when the IF statement is carried out. Learn how to create an array formula.

## Related

• Read an Overview of formulas. You'll learn all about the bits and pieces that make them up.

• Learn now to Use nested functions in a formula.

• Excel has 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.

• See a video on how to use the IF function.

Applies To: Excel for Mac 2011, Excel 2010, Excel 2016 for Mac, Excel Online, Excel Starter, Excel 2013, Excel 2016 Preview, Excel 2007

﻿