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

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



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language