IF function
This article describes the formula syntax and usage of the IFfunction in Microsoft 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:
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 Optional. 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_testargument
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_trueargument.
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
thevalue_if_false argument is omitted, (that is, there is no comma following the value_if_true argument),
the IFfunction returns the logical value FALSE. If logical_test evaluates to FALSE and the value of
the value_if_falseargument is blank (that is, there is only a 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
theLOOKUP function.)
If any of the arguments to IF are arrays, 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
theCOUNTIFS 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
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to
show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to
see all the data.
ACTUAL EXPENSE PREDICTED EXPENSE
$1,500 $900
$500 $900
$500 $925
=IF(A2>B2,"Over Because the actual expense of $1500 (A2) exceeded the predicted expense of $900 (B2),
Budget","OK") the result is Over Budget .
=IF(A2<B2,TRUE, The first IF function is false. Therefore, the second IF statement is calculated and because
IF(A3>B3,"over it too is false, the result is OK.
budget","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, The first IF function is false. Therefore, the second IF statement is calculated and because
IF(A3>B3,"over it too is false, the result is OK.
budget","OK"))